{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import influxdb\n",
    "import pandas as pd\n",
    "import dateutil.parser as dtparser\n",
    "import datetime\n",
    "import os\n",
    "import numpy as np\n",
    "import random\n",
    "from tqdm import tqdm\n",
    "import matplotlib.pyplot as plt\n",
    "from sklearn.decomposition import PCA\n",
    "from sklearn.metrics import r2_score\n",
    "#client = influxdb.DataFrameClient(host='192.168.38.176', port='8086', username='root', password='root', database='')\n",
    "#client = influxdb.DataFrameClient(host='192.168.38.2', port='8086', username='root', password='root', database='')\n",
    "client = influxdb.DataFrameClient(host='localhost', port=8086, username='root', password='', database='')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys\n",
    "sys.path.append('../../')\n",
    "import redis\n",
    "from Config.myConstant import *\n",
    "from Config.myConfig import *\n",
    "from Config.myFeaturesConstant import *\n",
    "from DataAccess.IndexComponentDataProcess import *\n",
    "from DataAccess.KLineDataProcess import *\n",
    "from DataAccess.TradedayDataProcess import *\n",
    "from DataAccess.IndustryClassification import *\n",
    "from DataAccess.IndexCode import *\n",
    "from DataAccess.StockSharesProcess import *\n",
    "from DataAccess.TickDataProcess import *\n",
    "from DataAccess.TickTransactionDataProcess import TickTransactionDataProcess\n",
    "from DataPrepare.dataPrepareByIndex import *\n",
    "from DataPrepare.dailyKLineDataPrepared import *\n",
    "from Strategy.stockReverseMovement import *\n",
    "from Strategy.myRandomForestForCeiling import *\n",
    "from Strategy.stockReverseByStd import *\n",
    "from Strategy.myAnalysisForReverseByStd import *\n",
    "from Strategy.stockMomentumByStd import *\n",
    "from Strategy.myAnalysisForMomentumByStd import *\n",
    "from Strategy.stockReverseByStdOnTick import *\n",
    "from Strategy.myAnalysisForReverseByTick import myAnalysisForReverseByTick\n",
    "from Strategy.myAnalysisForFactorsByDate import myAnalysisForFactorsByDate\n",
    "from DataPrepare.dailyFactorsProcess import *\n",
    "from DataPrepare.tickFactorsProcess import *\n",
    "from Strategy.stockIntradayByTick.reverse.strategy1 import strategy1\n",
    "from Strategy.stockIntradayByTick.momentum.strategy1 import strategyBreak\n",
    "\n",
    "from Strategy.baseStrategy.grade.gradeStrategy1 import gradeStrategy1\n",
    "from Strategy.baseStrategy.grade.gradeStrategyXgboost import gradeStrategyXgboost\n",
    "from Strategy.baseStrategy.grade.gradeStrategyDNN import gradeStrategyDNN\n",
    "from Strategy.baseStrategy.grade.gradeStrategyCNN import gradeStrategyCNN\n",
    "from Strategy.baseStrategy.grade.gradeStrategyCNNgroups import gradeStrategyCNNgroups\n",
    "from Strategy.baseStrategy.grade.gradeStrategyCNNparameters import gradeStrategyCNNparameters\n",
    "from Strategy.baseStrategy.grade.CnnParameters2 import CnnParameters2\n",
    "from Strategy.baseStrategy.grade.strategy20191112 import strategy20191112\n",
    "from Strategy.baseStrategy.grade.strategy20191115 import strategy20191115\n",
    "from Strategy.baseStrategy.grade.strategy20191115 import strategy20191115\n",
    "from Strategy.baseStrategy.grade.strategy20191202 import strategy20191202\n",
    "from Strategy.baseStrategy.grade.strategy20191224 import strategy20191224\n",
    "from Strategy.baseStrategy.grade.strategy20191228 import strategy20191228\n",
    "from Strategy.baseStrategy.grade.strategy20200110 import strategy20200110\n",
    "from Strategy.baseStrategy.grade.strategy20200123 import strategy20200123\n",
    "from Strategy.baseStrategy.grade.strategy20200315 import strategy20200315\n",
    "from Strategy.baseStrategy.strategyAnalysis import strategyAnalysis\n",
    "from Strategy.baseStrategy.grade.gradeStrategyTWAP import gradeStrategyTWAP\n",
    "\n",
    "from DataPrepare.tickFactors.tickDataPrepared import tickDataPrepared\n",
    "from DataAccess.MinuteKLineProcess import MinuteKLineProcess\n",
    "from DataAccess.DailyKLineProcess import DailyKLineProcess\n",
    "from DataPrepare.dailyFactorsWithInfluxdb.dailyDataPrepared import dailyDataPrepared\n",
    "#from alphaBackTest.backTest import backTest\n",
    "#from alphaBackTest.IndexFullReplication import IndexFullReplication \n",
    "#from T0DataCheck.dataCheck import dataCheck\n",
    "from Utility.mytest import *\n",
    "from Utility.JobLibUtility import *\n",
    "from Utility.UpdateBasicData import *\n",
    "import os\n",
    "os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'\n",
    "os.environ['NUMEXPR_MAX_THREADS'] = '16'\n",
    "import warnings\n",
    "import time\n",
    "from sklearn.metrics import r2_score, mean_squared_error\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import numpy as np\n",
    "#from MachineLearning.RNN.RNN001 import RNN001\n",
    "#from MachineLearning.XGBoost.xgboost001 import xgboost001\n",
    "#from MachineLearning.CNN.predictPerformance import predictPerformance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'1.8.9'"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "client.ping()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{'name': '_internal'}, {'name': 'test01'}, {'name': 'marketData'}, {'name': 'kehandesk'}, {'name': 'maodesk'}, {'name': 'alldesk'}]\n"
     ]
    }
   ],
   "source": [
    "dbs = client.get_list_database()\n",
    "print(dbs)\n",
    "#client.create_database('positiontest')\n",
    "#client.drop_database('MaoTickFactors20200430')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>date</th>\n",
       "      <th>tick</th>\n",
       "      <th>lastPrice</th>\n",
       "      <th>S1</th>\n",
       "      <th>S2</th>\n",
       "      <th>S3</th>\n",
       "      <th>S4</th>\n",
       "      <th>S5</th>\n",
       "      <th>S6</th>\n",
       "      <th>...</th>\n",
       "      <th>lowLimit</th>\n",
       "      <th>dailyOpen</th>\n",
       "      <th>dailyPreClose</th>\n",
       "      <th>transactions_count</th>\n",
       "      <th>weightedAvgBid</th>\n",
       "      <th>weightedAvgAsk</th>\n",
       "      <th>total_bid_size</th>\n",
       "      <th>total_ask_size</th>\n",
       "      <th>localRecordTime</th>\n",
       "      <th>tradeStatus</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mytime</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2021-12-21 09:15:04</th>\n",
       "      <td>600000.SH</td>\n",
       "      <td>20211221</td>\n",
       "      <td>091504000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.58</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>7.72</td>\n",
       "      <td>0.00</td>\n",
       "      <td>8.58</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>091504559567</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-12-21 09:15:07</th>\n",
       "      <td>600000.SH</td>\n",
       "      <td>20211221</td>\n",
       "      <td>091507000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.57</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>7.72</td>\n",
       "      <td>0.00</td>\n",
       "      <td>8.58</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>091507544326</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-12-21 09:15:10</th>\n",
       "      <td>600000.SH</td>\n",
       "      <td>20211221</td>\n",
       "      <td>091510000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.57</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>7.72</td>\n",
       "      <td>0.00</td>\n",
       "      <td>8.58</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>091510732239</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-12-21 09:15:16</th>\n",
       "      <td>600000.SH</td>\n",
       "      <td>20211221</td>\n",
       "      <td>091516000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.57</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>7.72</td>\n",
       "      <td>0.00</td>\n",
       "      <td>8.58</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>091516489499</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-12-21 09:15:46</th>\n",
       "      <td>600000.SH</td>\n",
       "      <td>20211221</td>\n",
       "      <td>091546000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.57</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>7.72</td>\n",
       "      <td>0.00</td>\n",
       "      <td>8.58</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>091546506502</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-12-21 14:59:50</th>\n",
       "      <td>600000.SH</td>\n",
       "      <td>20211221</td>\n",
       "      <td>145950000</td>\n",
       "      <td>8.62</td>\n",
       "      <td>8.61</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>7.72</td>\n",
       "      <td>8.55</td>\n",
       "      <td>8.58</td>\n",
       "      <td>13942.0</td>\n",
       "      <td>8.527</td>\n",
       "      <td>8.892</td>\n",
       "      <td>5602340.0</td>\n",
       "      <td>9544987.0</td>\n",
       "      <td>145951057667</td>\n",
       "      <td>U</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-12-21 14:59:53</th>\n",
       "      <td>600000.SH</td>\n",
       "      <td>20211221</td>\n",
       "      <td>145953000</td>\n",
       "      <td>8.62</td>\n",
       "      <td>8.61</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>7.72</td>\n",
       "      <td>8.55</td>\n",
       "      <td>8.58</td>\n",
       "      <td>13942.0</td>\n",
       "      <td>8.527</td>\n",
       "      <td>8.892</td>\n",
       "      <td>5602340.0</td>\n",
       "      <td>9544987.0</td>\n",
       "      <td>145954028597</td>\n",
       "      <td>U</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-12-21 14:59:56</th>\n",
       "      <td>600000.SH</td>\n",
       "      <td>20211221</td>\n",
       "      <td>145956000</td>\n",
       "      <td>8.62</td>\n",
       "      <td>8.61</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>7.72</td>\n",
       "      <td>8.55</td>\n",
       "      <td>8.58</td>\n",
       "      <td>13942.0</td>\n",
       "      <td>8.527</td>\n",
       "      <td>8.892</td>\n",
       "      <td>5602340.0</td>\n",
       "      <td>9544987.0</td>\n",
       "      <td>145957032874</td>\n",
       "      <td>U</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-12-21 14:59:59</th>\n",
       "      <td>600000.SH</td>\n",
       "      <td>20211221</td>\n",
       "      <td>145959000</td>\n",
       "      <td>8.62</td>\n",
       "      <td>8.61</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>7.72</td>\n",
       "      <td>8.55</td>\n",
       "      <td>8.58</td>\n",
       "      <td>13942.0</td>\n",
       "      <td>8.527</td>\n",
       "      <td>8.892</td>\n",
       "      <td>5602340.0</td>\n",
       "      <td>9544987.0</td>\n",
       "      <td>150000041344</td>\n",
       "      <td>U</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-12-21 15:00:00</th>\n",
       "      <td>600000.SH</td>\n",
       "      <td>20211221</td>\n",
       "      <td>150000000</td>\n",
       "      <td>8.61</td>\n",
       "      <td>8.62</td>\n",
       "      <td>8.63</td>\n",
       "      <td>8.64</td>\n",
       "      <td>8.65</td>\n",
       "      <td>8.66</td>\n",
       "      <td>8.67</td>\n",
       "      <td>...</td>\n",
       "      <td>7.72</td>\n",
       "      <td>8.55</td>\n",
       "      <td>8.58</td>\n",
       "      <td>14111.0</td>\n",
       "      <td>8.527</td>\n",
       "      <td>8.891</td>\n",
       "      <td>5675568.0</td>\n",
       "      <td>9577987.0</td>\n",
       "      <td>150000213238</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4767 rows × 57 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          code      date       tick  lastPrice    S1    S2  \\\n",
       "mytime                                                                       \n",
       "2021-12-21 09:15:04  600000.SH  20211221  091504000        NaN  8.58  0.00   \n",
       "2021-12-21 09:15:07  600000.SH  20211221  091507000        NaN  8.57  0.00   \n",
       "2021-12-21 09:15:10  600000.SH  20211221  091510000        NaN  8.57  0.00   \n",
       "2021-12-21 09:15:16  600000.SH  20211221  091516000        NaN  8.57  0.00   \n",
       "2021-12-21 09:15:46  600000.SH  20211221  091546000        NaN  8.57  0.00   \n",
       "...                        ...       ...        ...        ...   ...   ...   \n",
       "2021-12-21 14:59:50  600000.SH  20211221  145950000       8.62  8.61  0.00   \n",
       "2021-12-21 14:59:53  600000.SH  20211221  145953000       8.62  8.61  0.00   \n",
       "2021-12-21 14:59:56  600000.SH  20211221  145956000       8.62  8.61  0.00   \n",
       "2021-12-21 14:59:59  600000.SH  20211221  145959000       8.62  8.61  0.00   \n",
       "2021-12-21 15:00:00  600000.SH  20211221  150000000       8.61  8.62  8.63   \n",
       "\n",
       "                       S3    S4    S5    S6  ...  lowLimit  dailyOpen  \\\n",
       "mytime                                       ...                        \n",
       "2021-12-21 09:15:04  0.00  0.00  0.00  0.00  ...      7.72       0.00   \n",
       "2021-12-21 09:15:07  0.00  0.00  0.00  0.00  ...      7.72       0.00   \n",
       "2021-12-21 09:15:10  0.00  0.00  0.00  0.00  ...      7.72       0.00   \n",
       "2021-12-21 09:15:16  0.00  0.00  0.00  0.00  ...      7.72       0.00   \n",
       "2021-12-21 09:15:46  0.00  0.00  0.00  0.00  ...      7.72       0.00   \n",
       "...                   ...   ...   ...   ...  ...       ...        ...   \n",
       "2021-12-21 14:59:50  0.00  0.00  0.00  0.00  ...      7.72       8.55   \n",
       "2021-12-21 14:59:53  0.00  0.00  0.00  0.00  ...      7.72       8.55   \n",
       "2021-12-21 14:59:56  0.00  0.00  0.00  0.00  ...      7.72       8.55   \n",
       "2021-12-21 14:59:59  0.00  0.00  0.00  0.00  ...      7.72       8.55   \n",
       "2021-12-21 15:00:00  8.64  8.65  8.66  8.67  ...      7.72       8.55   \n",
       "\n",
       "                     dailyPreClose  transactions_count  weightedAvgBid  \\\n",
       "mytime                                                                   \n",
       "2021-12-21 09:15:04           8.58                 NaN             NaN   \n",
       "2021-12-21 09:15:07           8.58                 NaN             NaN   \n",
       "2021-12-21 09:15:10           8.58                 NaN             NaN   \n",
       "2021-12-21 09:15:16           8.58                 NaN             NaN   \n",
       "2021-12-21 09:15:46           8.58                 NaN             NaN   \n",
       "...                            ...                 ...             ...   \n",
       "2021-12-21 14:59:50           8.58             13942.0           8.527   \n",
       "2021-12-21 14:59:53           8.58             13942.0           8.527   \n",
       "2021-12-21 14:59:56           8.58             13942.0           8.527   \n",
       "2021-12-21 14:59:59           8.58             13942.0           8.527   \n",
       "2021-12-21 15:00:00           8.58             14111.0           8.527   \n",
       "\n",
       "                     weightedAvgAsk  total_bid_size  total_ask_size  \\\n",
       "mytime                                                                \n",
       "2021-12-21 09:15:04             NaN             NaN             NaN   \n",
       "2021-12-21 09:15:07             NaN             NaN             NaN   \n",
       "2021-12-21 09:15:10             NaN             NaN             NaN   \n",
       "2021-12-21 09:15:16             NaN             NaN             NaN   \n",
       "2021-12-21 09:15:46             NaN             NaN             NaN   \n",
       "...                             ...             ...             ...   \n",
       "2021-12-21 14:59:50           8.892       5602340.0       9544987.0   \n",
       "2021-12-21 14:59:53           8.892       5602340.0       9544987.0   \n",
       "2021-12-21 14:59:56           8.892       5602340.0       9544987.0   \n",
       "2021-12-21 14:59:59           8.892       5602340.0       9544987.0   \n",
       "2021-12-21 15:00:00           8.891       5675568.0       9577987.0   \n",
       "\n",
       "                     localRecordTime  tradeStatus  \n",
       "mytime                                             \n",
       "2021-12-21 09:15:04     091504559567          C    \n",
       "2021-12-21 09:15:07     091507544326          C    \n",
       "2021-12-21 09:15:10     091510732239          C    \n",
       "2021-12-21 09:15:16     091516489499          C    \n",
       "2021-12-21 09:15:46     091546506502          C    \n",
       "...                              ...          ...  \n",
       "2021-12-21 14:59:50     145951057667          U    \n",
       "2021-12-21 14:59:53     145954028597          U    \n",
       "2021-12-21 14:59:56     145957032874          U    \n",
       "2021-12-21 14:59:59     150000041344          U    \n",
       "2021-12-21 15:00:00     150000213238          A    \n",
       "\n",
       "[4767 rows x 57 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from DataAccess.TickDataProcess import TickDataProcess\n",
    "from DataAccess.StockIPOInfoProcess import *\n",
    "import pymssql\n",
    "#202103-202212,交易日\n",
    "code='600000.SH'\n",
    "datestart=20211221\n",
    "dateend=20211231\n",
    "\n",
    "t = TickDataProcess()\n",
    "data = t.getTickShotDataFromSql(code,datestart)\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['20210113', '20210201']\n",
      "['20210111', '20210201']\n",
      "20210111\n",
      "20210201\n"
     ]
    }
   ],
   "source": [
    "da = ['20210111','20210112','20210113','20210201']\n",
    "da1 = list(set([i[:6]  for i in da]))\n",
    "cal = pd.value_counts([i[:6]  for i in da])\n",
    "cal.to_dict()\n",
    "jishu = [cal[i] for i in da1]\n",
    "endlist = [da[i-1] for i in np.cumsum(jishu).tolist()]\n",
    "print(endlist)\n",
    "jishu = [i for i in np.cumsum(jishu).tolist()[:-1]]\n",
    "jishu = [0] + jishu\n",
    "startlist = [da[i] for i in jishu]\n",
    "print(startlist)\n",
    "for i in zip(startlist,endlist):\n",
    "    print(i[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "#创建数据库\n",
    "#client.create_database('test01')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#切换至数据库\n",
    "client.switch_database('test01')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "'''\n",
    "def saveDataFrameDataToInfluxdbWithClient(client,data:pd.DataFrame,database:str,measurement:str,tag:dict,connet=InfluxdbServer):\n",
    "        \n",
    "    dbs = client.get_list_database()\n",
    "    if ({'name':database} in dbs)==False:\n",
    "        client.create_database(database)\n",
    "    success=False\n",
    "    myerror=''\n",
    "    for i in range(10):\n",
    "        try:\n",
    "            client.write_points(dataframe=data,\n",
    "                    database=database,\n",
    "                    measurement=measurement,\n",
    "                    tags=tag,\n",
    "                    field_columns=list(data.columns),\n",
    "                    protocol='line',\n",
    "                    batch_size=10000)\n",
    "            success=True\n",
    "            break\n",
    "        except Exception as excp:\n",
    "            myerror=excp\n",
    "            pass\n",
    "    if success==False:\n",
    "        logger.error(f'save data to influxdb {measurement} in {tag} error! {myerror}')\n",
    "    pass\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "#from Utility.InfluxdbUtility import *\n",
    "#insert measurements_test01,data.shape[0]\n",
    "#存数据\n",
    "#sdata = InfluxdbUtility()\n",
    "saveDataFrameDataToInfluxdbWithClient(client,data,'test01','m-test01',{})\n",
    "#取数据\n",
    "#getDataFromInfluxdb\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "for i in range(10):\n",
    "    try:\n",
    "        client.write_points(dataframe=data,\n",
    "            database='test01',\n",
    "            measurement='table01',\n",
    "            tags=tag,\n",
    "            field_columns=list(data.columns),\n",
    "            protocol='line',\n",
    "            batch_size=10000)\n",
    "        success=True\n",
    "        break\n",
    "    except Exception as excp:\n",
    "        myerror=excp\n",
    "        pass\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\ndbs = client.get_list_database()\\nprint(dbs)\\nprint(help(client.write_points))\\n'"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''\n",
    "dbs = client.get_list_database()\n",
    "print(dbs)\n",
    "print(help(client.write_points))\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# database='DailyData_Gus'\n",
    "# measure='marketData'\n",
    "# #measure='test'\n",
    "# date=20200101\n",
    "# b = dtparser.parse(str(date))+ datetime.timedelta(hours=0)\n",
    "# e = dtparser.parse(str(date)) + datetime.timedelta(hours=240)\n",
    "# query=f\"\"\" select * from \"{database}\".\"autogen\".\"{measure}\" where time >= {int(b.timestamp() * 1000 * 1000 * 1000)} and time < {int(e.timestamp() * 1000 * 1000* 1000)} \"\"\"\n",
    "# #query=f\"\"\" select * from \"{database}\" \"\"\"\n",
    "# #query=f\"\"\"SELECT * FROM \\\"cpptest\\\".\\\"autogen\\\".\\\"601318.SH\\\"  where time >= '2019-09-24T09:00:00Z' and time < '2019-09-24T16:00:00Z' order by time \"\"\"\n",
    "# #query=f\"\"\"SELECT \\\"stkcd\\\",  \\\"cp\\\", \\\"OPNPRC\\\",\\\"PRECLOSE\\\", \\\"HighLimit\\\",\\\"LowLimit\\\",\\\"hp\\\",\\\"lp\\\",\\\"ts\\\",\\\"tt\\\", \\\"B1\\\",\\\"B2\\\",\\\"B3\\\",\\\"B4\\\",\\\"B5\\\",\\\"B6\\\",\\\"B7\\\",\\\"B8\\\",\\\"B9\\\",\\\"B10\\\",\\\"BV1\\\",\\\"BV2\\\",\\\"BV3\\\",\\\"BV4\\\",\\\"BV5\\\",\\\"BV6\\\",\\\"BV7\\\",\\\"BV8\\\",\\\"BV9\\\",\\\"BV10\\\",\\\"S1\\\", \\\"S2\\\",\\\"S3\\\",\\\"S4\\\",\\\"S5\\\",\\\"S6\\\",\\\"S7\\\",\\\"S8\\\",\\\"S9\\\",\\\"S10\\\",\\\"SV1\\\",\\\"SV2\\\",\\\"SV3\\\",\\\"SV4\\\",\\\"SV5\\\",\\\"SV6\\\",\\\"SV7\\\",\\\"SV8\\\",\\\"SV9\\\" ,\\\"SV10\\\" FROM \\\"cpptest\\\".\\\"autogen\\\".\\\"{measure}\\\"  where time >= '2019-09-24T09:00:00Z' and time < '2019-09-24T16:00:00Z' order by time \"\"\"\n",
    "                         \n",
    "# result=client.query(query)\n",
    "# if result!={}:\n",
    "#     data=pd.DataFrame(result[measure])\n",
    "# else:\n",
    "#     data=pd.DataFrame()\n",
    "# data1=data\n",
    "# data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "'m-test01'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-29-39122d3f6e29>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m     12\u001b[0m \u001b[0mresult\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mclient\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mquery\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     13\u001b[0m \u001b[1;31m#将取出来的数据整理为数据字典\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 14\u001b[1;33m \u001b[0mmydata_save\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mDataFrame\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mresult\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mmeasure\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     15\u001b[0m \u001b[0mmydata_save\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mKeyError\u001b[0m: 'm-test01'"
     ]
    }
   ],
   "source": [
    "#存数据\n",
    "#saveDataFrameDataToInfluxdbWithClient(client,data,'test01','m-test01',{})\n",
    "#取数据\n",
    "startDate=20211231\n",
    "endDate=20220119\n",
    "database='test01'\n",
    "measure='m-test01'\n",
    "b = dtparser.parse(str(startDate))+ datetime.timedelta(hours=0)\n",
    "e = dtparser.parse(str(endDate)) + datetime.timedelta(hours=24)\n",
    "#print(b,e)\n",
    "query=f\"\"\" select * from \"{database}\".\"autogen\".\"{measure}\" where time >= {int(b.timestamp() * 1000 * 1000 * 1000)} and time <= {int(e.timestamp() * 1000 * 1000* 1000)} \"\"\"\n",
    "result=client.query(query)\n",
    "#将取出来的数据整理为数据字典\n",
    "mydata_save = pd.DataFrame(result[measure])\n",
    "mydata_save"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Help on method dropna in module pandas.core.frame:\n",
      "\n",
      "dropna(axis=0, how='any', thresh=None, subset=None, inplace=False) method of pandas.core.frame.DataFrame instance\n",
      "    Remove missing values.\n",
      "    \n",
      "    See the :ref:`User Guide <missing_data>` for more on which values are\n",
      "    considered missing, and how to work with missing data.\n",
      "    \n",
      "    Parameters\n",
      "    ----------\n",
      "    axis : {0 or 'index', 1 or 'columns'}, default 0\n",
      "        Determine if rows or columns which contain missing values are\n",
      "        removed.\n",
      "    \n",
      "        * 0, or 'index' : Drop rows which contain missing values.\n",
      "        * 1, or 'columns' : Drop columns which contain missing value.\n",
      "    \n",
      "        .. versionchanged:: 1.0.0\n",
      "    \n",
      "           Pass tuple or list to drop on multiple axes.\n",
      "           Only a single axis is allowed.\n",
      "    \n",
      "    how : {'any', 'all'}, default 'any'\n",
      "        Determine if row or column is removed from DataFrame, when we have\n",
      "        at least one NA or all NA.\n",
      "    \n",
      "        * 'any' : If any NA values are present, drop that row or column.\n",
      "        * 'all' : If all values are NA, drop that row or column.\n",
      "    \n",
      "    thresh : int, optional\n",
      "        Require that many non-NA values.\n",
      "    subset : array-like, optional\n",
      "        Labels along other axis to consider, e.g. if you are dropping rows\n",
      "        these would be a list of columns to include.\n",
      "    inplace : bool, default False\n",
      "        If True, do operation inplace and return None.\n",
      "    \n",
      "    Returns\n",
      "    -------\n",
      "    DataFrame or None\n",
      "        DataFrame with NA entries dropped from it or None if ``inplace=True``.\n",
      "    \n",
      "    See Also\n",
      "    --------\n",
      "    DataFrame.isna: Indicate missing values.\n",
      "    DataFrame.notna : Indicate existing (non-missing) values.\n",
      "    DataFrame.fillna : Replace missing values.\n",
      "    Series.dropna : Drop missing values.\n",
      "    Index.dropna : Drop missing indices.\n",
      "    \n",
      "    Examples\n",
      "    --------\n",
      "    >>> df = pd.DataFrame({\"name\": ['Alfred', 'Batman', 'Catwoman'],\n",
      "    ...                    \"toy\": [np.nan, 'Batmobile', 'Bullwhip'],\n",
      "    ...                    \"born\": [pd.NaT, pd.Timestamp(\"1940-04-25\"),\n",
      "    ...                             pd.NaT]})\n",
      "    >>> df\n",
      "           name        toy       born\n",
      "    0    Alfred        NaN        NaT\n",
      "    1    Batman  Batmobile 1940-04-25\n",
      "    2  Catwoman   Bullwhip        NaT\n",
      "    \n",
      "    Drop the rows where at least one element is missing.\n",
      "    \n",
      "    >>> df.dropna()\n",
      "         name        toy       born\n",
      "    1  Batman  Batmobile 1940-04-25\n",
      "    \n",
      "    Drop the columns where at least one element is missing.\n",
      "    \n",
      "    >>> df.dropna(axis='columns')\n",
      "           name\n",
      "    0    Alfred\n",
      "    1    Batman\n",
      "    2  Catwoman\n",
      "    \n",
      "    Drop the rows where all elements are missing.\n",
      "    \n",
      "    >>> df.dropna(how='all')\n",
      "           name        toy       born\n",
      "    0    Alfred        NaN        NaT\n",
      "    1    Batman  Batmobile 1940-04-25\n",
      "    2  Catwoman   Bullwhip        NaT\n",
      "    \n",
      "    Keep only the rows with at least 2 non-NA values.\n",
      "    \n",
      "    >>> df.dropna(thresh=2)\n",
      "           name        toy       born\n",
      "    1    Batman  Batmobile 1940-04-25\n",
      "    2  Catwoman   Bullwhip        NaT\n",
      "    \n",
      "    Define in which columns to look for missing values.\n",
      "    \n",
      "    >>> df.dropna(subset=['name', 'toy'])\n",
      "           name        toy       born\n",
      "    1    Batman  Batmobile 1940-04-25\n",
      "    2  Catwoman   Bullwhip        NaT\n",
      "    \n",
      "    Keep the DataFrame with valid entries in the same variable.\n",
      "    \n",
      "    >>> df.dropna(inplace=True)\n",
      "    >>> df\n",
      "         name        toy       born\n",
      "    1  Batman  Batmobile 1940-04-25\n",
      "\n"
     ]
    }
   ],
   "source": [
    "#数据预处理相关代码\n",
    "#df = df.dropna(subset['列名','列名'],how='any')\n",
    "#删除数据字典中任意的在列‘’，‘’为空值的行(axis = 0);how = 'any' or 'all' 空缺条件any其一满足就删除；all都满足就删除\n",
    "help(mydata_save.dropna)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(len(mydays)-1):\n",
    "    date1=mydays[i]\n",
    "    date2=mydays[i+1]\n",
    "    print(f\"{date1}-{date2}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "database='test01'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['20100104',\n",
       " '20100105',\n",
       " '20100106',\n",
       " '20100107',\n",
       " '20100108',\n",
       " '20100111',\n",
       " '20100112',\n",
       " '20100113',\n",
       " '20100114',\n",
       " '20100115',\n",
       " '20100118',\n",
       " '20100119',\n",
       " '20100120',\n",
       " '20100121',\n",
       " '20100122',\n",
       " '20100125',\n",
       " '20100126',\n",
       " '20100127',\n",
       " '20100128',\n",
       " '20100129',\n",
       " '20100201',\n",
       " '20100202',\n",
       " '20100203',\n",
       " '20100204',\n",
       " '20100205',\n",
       " '20100208',\n",
       " '20100209',\n",
       " '20100210',\n",
       " '20100211',\n",
       " '20100212',\n",
       " '20100222',\n",
       " '20100223',\n",
       " '20100224',\n",
       " '20100225',\n",
       " '20100226',\n",
       " '20100301',\n",
       " '20100302',\n",
       " '20100303',\n",
       " '20100304',\n",
       " '20100305',\n",
       " '20100308',\n",
       " '20100309',\n",
       " '20100310',\n",
       " '20100311',\n",
       " '20100312',\n",
       " '20100315',\n",
       " '20100316',\n",
       " '20100317',\n",
       " '20100318',\n",
       " '20100319',\n",
       " '20100322',\n",
       " '20100323',\n",
       " '20100324',\n",
       " '20100325',\n",
       " '20100326',\n",
       " '20100329',\n",
       " '20100330',\n",
       " '20100331',\n",
       " '20100401',\n",
       " '20100402',\n",
       " '20100406',\n",
       " '20100407',\n",
       " '20100408',\n",
       " '20100409',\n",
       " '20100412',\n",
       " '20100413',\n",
       " '20100414',\n",
       " '20100415',\n",
       " '20100416',\n",
       " '20100419',\n",
       " '20100420',\n",
       " '20100421',\n",
       " '20100422',\n",
       " '20100423',\n",
       " '20100426',\n",
       " '20100427',\n",
       " '20100428',\n",
       " '20100429',\n",
       " '20100430',\n",
       " '20100504',\n",
       " '20100505',\n",
       " '20100506',\n",
       " '20100507',\n",
       " '20100510',\n",
       " '20100511',\n",
       " '20100512',\n",
       " '20100513',\n",
       " '20100514',\n",
       " '20100517',\n",
       " '20100518',\n",
       " '20100519',\n",
       " '20100520',\n",
       " '20100521',\n",
       " '20100524',\n",
       " '20100525',\n",
       " '20100526',\n",
       " '20100527',\n",
       " '20100528',\n",
       " '20100531',\n",
       " '20100601',\n",
       " '20100602',\n",
       " '20100603',\n",
       " '20100604',\n",
       " '20100607',\n",
       " '20100608',\n",
       " '20100609',\n",
       " '20100610',\n",
       " '20100611',\n",
       " '20100617',\n",
       " '20100618',\n",
       " '20100621',\n",
       " '20100622',\n",
       " '20100623',\n",
       " '20100624',\n",
       " '20100625',\n",
       " '20100628',\n",
       " '20100629',\n",
       " '20100630',\n",
       " '20100701',\n",
       " '20100702',\n",
       " '20100705',\n",
       " '20100706',\n",
       " '20100707',\n",
       " '20100708',\n",
       " '20100709',\n",
       " '20100712',\n",
       " '20100713',\n",
       " '20100714',\n",
       " '20100715',\n",
       " '20100716',\n",
       " '20100719',\n",
       " '20100720',\n",
       " '20100721',\n",
       " '20100722',\n",
       " '20100723',\n",
       " '20100726',\n",
       " '20100727',\n",
       " '20100728',\n",
       " '20100729',\n",
       " '20100730',\n",
       " '20100802',\n",
       " '20100803',\n",
       " '20100804',\n",
       " '20100805',\n",
       " '20100806',\n",
       " '20100809',\n",
       " '20100810',\n",
       " '20100811',\n",
       " '20100812',\n",
       " '20100813',\n",
       " '20100816',\n",
       " '20100817',\n",
       " '20100818',\n",
       " '20100819',\n",
       " '20100820',\n",
       " '20100823',\n",
       " '20100824',\n",
       " '20100825',\n",
       " '20100826',\n",
       " '20100827',\n",
       " '20100830',\n",
       " '20100831',\n",
       " '20100901',\n",
       " '20100902',\n",
       " '20100903',\n",
       " '20100906',\n",
       " '20100907',\n",
       " '20100908',\n",
       " '20100909',\n",
       " '20100910',\n",
       " '20100913',\n",
       " '20100914',\n",
       " '20100915',\n",
       " '20100916',\n",
       " '20100917',\n",
       " '20100920',\n",
       " '20100921',\n",
       " '20100927',\n",
       " '20100928',\n",
       " '20100929',\n",
       " '20100930',\n",
       " '20101008',\n",
       " '20101011',\n",
       " '20101012',\n",
       " '20101013',\n",
       " '20101014',\n",
       " '20101015',\n",
       " '20101018',\n",
       " '20101019',\n",
       " '20101020',\n",
       " '20101021',\n",
       " '20101022',\n",
       " '20101025',\n",
       " '20101026',\n",
       " '20101027',\n",
       " '20101028',\n",
       " '20101029',\n",
       " '20101101',\n",
       " '20101102',\n",
       " '20101103',\n",
       " '20101104',\n",
       " '20101105',\n",
       " '20101108',\n",
       " '20101109',\n",
       " '20101110',\n",
       " '20101111',\n",
       " '20101112',\n",
       " '20101115',\n",
       " '20101116',\n",
       " '20101117',\n",
       " '20101118',\n",
       " '20101119',\n",
       " '20101122',\n",
       " '20101123',\n",
       " '20101124',\n",
       " '20101125',\n",
       " '20101126',\n",
       " '20101129',\n",
       " '20101130',\n",
       " '20101201',\n",
       " '20101202',\n",
       " '20101203',\n",
       " '20101206',\n",
       " '20101207',\n",
       " '20101208',\n",
       " '20101209',\n",
       " '20101210',\n",
       " '20101213',\n",
       " '20101214',\n",
       " '20101215',\n",
       " '20101216',\n",
       " '20101217',\n",
       " '20101220',\n",
       " '20101221',\n",
       " '20101222',\n",
       " '20101223',\n",
       " '20101224',\n",
       " '20101227',\n",
       " '20101228',\n",
       " '20101229',\n",
       " '20101230',\n",
       " '20101231',\n",
       " '20110104',\n",
       " '20110105',\n",
       " '20110106',\n",
       " '20110107',\n",
       " '20110110',\n",
       " '20110111',\n",
       " '20110112',\n",
       " '20110113',\n",
       " '20110114',\n",
       " '20110117',\n",
       " '20110118',\n",
       " '20110119',\n",
       " '20110120',\n",
       " '20110121',\n",
       " '20110124',\n",
       " '20110125',\n",
       " '20110126',\n",
       " '20110127',\n",
       " '20110128',\n",
       " '20110131',\n",
       " '20110201',\n",
       " '20110209',\n",
       " '20110210',\n",
       " '20110211',\n",
       " '20110214',\n",
       " '20110215',\n",
       " '20110216',\n",
       " '20110217',\n",
       " '20110218',\n",
       " '20110221',\n",
       " '20110222',\n",
       " '20110223',\n",
       " '20110224',\n",
       " '20110225',\n",
       " '20110228',\n",
       " '20110301',\n",
       " '20110302',\n",
       " '20110303',\n",
       " '20110304',\n",
       " '20110307',\n",
       " '20110308',\n",
       " '20110309',\n",
       " '20110310',\n",
       " '20110311',\n",
       " '20110314',\n",
       " '20110315',\n",
       " '20110316',\n",
       " '20110317',\n",
       " '20110318',\n",
       " '20110321',\n",
       " '20110322',\n",
       " '20110323',\n",
       " '20110324',\n",
       " '20110325',\n",
       " '20110328',\n",
       " '20110329',\n",
       " '20110330',\n",
       " '20110331',\n",
       " '20110401',\n",
       " '20110406',\n",
       " '20110407',\n",
       " '20110408',\n",
       " '20110411',\n",
       " '20110412',\n",
       " '20110413',\n",
       " '20110414',\n",
       " '20110415',\n",
       " '20110418',\n",
       " '20110419',\n",
       " '20110420',\n",
       " '20110421',\n",
       " '20110422',\n",
       " '20110425',\n",
       " '20110426',\n",
       " '20110427',\n",
       " '20110428',\n",
       " '20110429',\n",
       " '20110503',\n",
       " '20110504',\n",
       " '20110505',\n",
       " '20110506',\n",
       " '20110509',\n",
       " '20110510',\n",
       " '20110511',\n",
       " '20110512',\n",
       " '20110513',\n",
       " '20110516',\n",
       " '20110517',\n",
       " '20110518',\n",
       " '20110519',\n",
       " '20110520',\n",
       " '20110523',\n",
       " '20110524',\n",
       " '20110525',\n",
       " '20110526',\n",
       " '20110527',\n",
       " '20110530',\n",
       " '20110531',\n",
       " '20110601',\n",
       " '20110602',\n",
       " '20110603',\n",
       " '20110607',\n",
       " '20110608',\n",
       " '20110609',\n",
       " '20110610',\n",
       " '20110613',\n",
       " '20110614',\n",
       " '20110615',\n",
       " '20110616',\n",
       " '20110617',\n",
       " '20110620',\n",
       " '20110621',\n",
       " '20110622',\n",
       " '20110623',\n",
       " '20110624',\n",
       " '20110627',\n",
       " '20110628',\n",
       " '20110629',\n",
       " '20110630',\n",
       " '20110701',\n",
       " '20110704',\n",
       " '20110705',\n",
       " '20110706',\n",
       " '20110707',\n",
       " '20110708',\n",
       " '20110711',\n",
       " '20110712',\n",
       " '20110713',\n",
       " '20110714',\n",
       " '20110715',\n",
       " '20110718',\n",
       " '20110719',\n",
       " '20110720',\n",
       " '20110721',\n",
       " '20110722',\n",
       " '20110725',\n",
       " '20110726',\n",
       " '20110727',\n",
       " '20110728',\n",
       " '20110729',\n",
       " '20110801',\n",
       " '20110802',\n",
       " '20110803',\n",
       " '20110804',\n",
       " '20110805',\n",
       " '20110808',\n",
       " '20110809',\n",
       " '20110810',\n",
       " '20110811',\n",
       " '20110812',\n",
       " '20110815',\n",
       " '20110816',\n",
       " '20110817',\n",
       " '20110818',\n",
       " '20110819',\n",
       " '20110822',\n",
       " '20110823',\n",
       " '20110824',\n",
       " '20110825',\n",
       " '20110826',\n",
       " '20110829',\n",
       " '20110830',\n",
       " '20110831',\n",
       " '20110901',\n",
       " '20110902',\n",
       " '20110905',\n",
       " '20110906',\n",
       " '20110907',\n",
       " '20110908',\n",
       " '20110909',\n",
       " '20110913',\n",
       " '20110914',\n",
       " '20110915',\n",
       " '20110916',\n",
       " '20110919',\n",
       " '20110920',\n",
       " '20110921',\n",
       " '20110922',\n",
       " '20110923',\n",
       " '20110926',\n",
       " '20110927',\n",
       " '20110928',\n",
       " '20110929',\n",
       " '20110930',\n",
       " '20111010',\n",
       " '20111011',\n",
       " '20111012',\n",
       " '20111013',\n",
       " '20111014',\n",
       " '20111017',\n",
       " '20111018',\n",
       " '20111019',\n",
       " '20111020',\n",
       " '20111021',\n",
       " '20111024',\n",
       " '20111025',\n",
       " '20111026',\n",
       " '20111027',\n",
       " '20111028',\n",
       " '20111031',\n",
       " '20111101',\n",
       " '20111102',\n",
       " '20111103',\n",
       " '20111104',\n",
       " '20111107',\n",
       " '20111108',\n",
       " '20111109',\n",
       " '20111110',\n",
       " '20111111',\n",
       " '20111114',\n",
       " '20111115',\n",
       " '20111116',\n",
       " '20111117',\n",
       " '20111118',\n",
       " '20111121',\n",
       " '20111122',\n",
       " '20111123',\n",
       " '20111124',\n",
       " '20111125',\n",
       " '20111128',\n",
       " '20111129',\n",
       " '20111130',\n",
       " '20111201',\n",
       " '20111202',\n",
       " '20111205',\n",
       " '20111206',\n",
       " '20111207',\n",
       " '20111208',\n",
       " '20111209',\n",
       " '20111212',\n",
       " '20111213',\n",
       " '20111214',\n",
       " '20111215',\n",
       " '20111216',\n",
       " '20111219',\n",
       " '20111220',\n",
       " '20111221',\n",
       " '20111222',\n",
       " '20111223',\n",
       " '20111226',\n",
       " '20111227',\n",
       " '20111228',\n",
       " '20111229',\n",
       " '20111230',\n",
       " '20120104',\n",
       " '20120105',\n",
       " '20120106',\n",
       " '20120109',\n",
       " '20120110',\n",
       " '20120111',\n",
       " '20120112',\n",
       " '20120113',\n",
       " '20120116',\n",
       " '20120117',\n",
       " '20120118',\n",
       " '20120119',\n",
       " '20120120',\n",
       " '20120130',\n",
       " '20120131',\n",
       " '20120201',\n",
       " '20120202',\n",
       " '20120203',\n",
       " '20120206',\n",
       " '20120207',\n",
       " '20120208',\n",
       " '20120209',\n",
       " '20120210',\n",
       " '20120213',\n",
       " '20120214',\n",
       " '20120215',\n",
       " '20120216',\n",
       " '20120217',\n",
       " '20120220',\n",
       " '20120221',\n",
       " '20120222',\n",
       " '20120223',\n",
       " '20120224',\n",
       " '20120227',\n",
       " '20120228',\n",
       " '20120229',\n",
       " '20120301',\n",
       " '20120302',\n",
       " '20120305',\n",
       " '20120306',\n",
       " '20120307',\n",
       " '20120308',\n",
       " '20120309',\n",
       " '20120312',\n",
       " '20120313',\n",
       " '20120314',\n",
       " '20120315',\n",
       " '20120316',\n",
       " '20120319',\n",
       " '20120320',\n",
       " '20120321',\n",
       " '20120322',\n",
       " '20120323',\n",
       " '20120326',\n",
       " '20120327',\n",
       " '20120328',\n",
       " '20120329',\n",
       " '20120330',\n",
       " '20120405',\n",
       " '20120406',\n",
       " '20120409',\n",
       " '20120410',\n",
       " '20120411',\n",
       " '20120412',\n",
       " '20120413',\n",
       " '20120416',\n",
       " '20120417',\n",
       " '20120418',\n",
       " '20120419',\n",
       " '20120420',\n",
       " '20120423',\n",
       " '20120424',\n",
       " '20120425',\n",
       " '20120426',\n",
       " '20120427',\n",
       " '20120502',\n",
       " '20120503',\n",
       " '20120504',\n",
       " '20120507',\n",
       " '20120508',\n",
       " '20120509',\n",
       " '20120510',\n",
       " '20120511',\n",
       " '20120514',\n",
       " '20120515',\n",
       " '20120516',\n",
       " '20120517',\n",
       " '20120518',\n",
       " '20120521',\n",
       " '20120522',\n",
       " '20120523',\n",
       " '20120524',\n",
       " '20120525',\n",
       " '20120528',\n",
       " '20120529',\n",
       " '20120530',\n",
       " '20120531',\n",
       " '20120601',\n",
       " '20120604',\n",
       " '20120605',\n",
       " '20120606',\n",
       " '20120607',\n",
       " '20120608',\n",
       " '20120611',\n",
       " '20120612',\n",
       " '20120613',\n",
       " '20120614',\n",
       " '20120615',\n",
       " '20120618',\n",
       " '20120619',\n",
       " '20120620',\n",
       " '20120621',\n",
       " '20120625',\n",
       " '20120626',\n",
       " '20120627',\n",
       " '20120628',\n",
       " '20120629',\n",
       " '20120702',\n",
       " '20120703',\n",
       " '20120704',\n",
       " '20120705',\n",
       " '20120706',\n",
       " '20120709',\n",
       " '20120710',\n",
       " '20120711',\n",
       " '20120712',\n",
       " '20120713',\n",
       " '20120716',\n",
       " '20120717',\n",
       " '20120718',\n",
       " '20120719',\n",
       " '20120720',\n",
       " '20120723',\n",
       " '20120724',\n",
       " '20120725',\n",
       " '20120726',\n",
       " '20120727',\n",
       " '20120730',\n",
       " '20120731',\n",
       " '20120801',\n",
       " '20120802',\n",
       " '20120803',\n",
       " '20120806',\n",
       " '20120807',\n",
       " '20120808',\n",
       " '20120809',\n",
       " '20120810',\n",
       " '20120813',\n",
       " '20120814',\n",
       " '20120815',\n",
       " '20120816',\n",
       " '20120817',\n",
       " '20120820',\n",
       " '20120821',\n",
       " '20120822',\n",
       " '20120823',\n",
       " '20120824',\n",
       " '20120827',\n",
       " '20120828',\n",
       " '20120829',\n",
       " '20120830',\n",
       " '20120831',\n",
       " '20120903',\n",
       " '20120904',\n",
       " '20120905',\n",
       " '20120906',\n",
       " '20120907',\n",
       " '20120910',\n",
       " '20120911',\n",
       " '20120912',\n",
       " '20120913',\n",
       " '20120914',\n",
       " '20120917',\n",
       " '20120918',\n",
       " '20120919',\n",
       " '20120920',\n",
       " '20120921',\n",
       " '20120924',\n",
       " '20120925',\n",
       " '20120926',\n",
       " '20120927',\n",
       " '20120928',\n",
       " '20121008',\n",
       " '20121009',\n",
       " '20121010',\n",
       " '20121011',\n",
       " '20121012',\n",
       " '20121015',\n",
       " '20121016',\n",
       " '20121017',\n",
       " '20121018',\n",
       " '20121019',\n",
       " '20121022',\n",
       " '20121023',\n",
       " '20121024',\n",
       " '20121025',\n",
       " '20121026',\n",
       " '20121029',\n",
       " '20121030',\n",
       " '20121031',\n",
       " '20121101',\n",
       " '20121102',\n",
       " '20121105',\n",
       " '20121106',\n",
       " '20121107',\n",
       " '20121108',\n",
       " '20121109',\n",
       " '20121112',\n",
       " '20121113',\n",
       " '20121114',\n",
       " '20121115',\n",
       " '20121116',\n",
       " '20121119',\n",
       " '20121120',\n",
       " '20121121',\n",
       " '20121122',\n",
       " '20121123',\n",
       " '20121126',\n",
       " '20121127',\n",
       " '20121128',\n",
       " '20121129',\n",
       " '20121130',\n",
       " '20121203',\n",
       " '20121204',\n",
       " '20121205',\n",
       " '20121206',\n",
       " '20121207',\n",
       " '20121210',\n",
       " '20121211',\n",
       " '20121212',\n",
       " '20121213',\n",
       " '20121214',\n",
       " '20121217',\n",
       " '20121218',\n",
       " '20121219',\n",
       " '20121220',\n",
       " '20121221',\n",
       " '20121224',\n",
       " '20121225',\n",
       " '20121226',\n",
       " '20121227',\n",
       " '20121228',\n",
       " '20121231',\n",
       " '20130104',\n",
       " '20130107',\n",
       " '20130108',\n",
       " '20130109',\n",
       " '20130110',\n",
       " '20130111',\n",
       " '20130114',\n",
       " '20130115',\n",
       " '20130116',\n",
       " '20130117',\n",
       " '20130118',\n",
       " '20130121',\n",
       " '20130122',\n",
       " '20130123',\n",
       " '20130124',\n",
       " '20130125',\n",
       " '20130128',\n",
       " '20130129',\n",
       " '20130130',\n",
       " '20130131',\n",
       " '20130201',\n",
       " '20130204',\n",
       " '20130205',\n",
       " '20130206',\n",
       " '20130207',\n",
       " '20130208',\n",
       " '20130218',\n",
       " '20130219',\n",
       " '20130220',\n",
       " '20130221',\n",
       " '20130222',\n",
       " '20130225',\n",
       " '20130226',\n",
       " '20130227',\n",
       " '20130228',\n",
       " '20130301',\n",
       " '20130304',\n",
       " '20130305',\n",
       " '20130306',\n",
       " '20130307',\n",
       " '20130308',\n",
       " '20130311',\n",
       " '20130312',\n",
       " '20130313',\n",
       " '20130314',\n",
       " '20130315',\n",
       " '20130318',\n",
       " '20130319',\n",
       " '20130320',\n",
       " '20130321',\n",
       " '20130322',\n",
       " '20130325',\n",
       " '20130326',\n",
       " '20130327',\n",
       " '20130328',\n",
       " '20130329',\n",
       " '20130401',\n",
       " '20130402',\n",
       " '20130403',\n",
       " '20130408',\n",
       " '20130409',\n",
       " '20130410',\n",
       " '20130411',\n",
       " '20130412',\n",
       " '20130415',\n",
       " '20130416',\n",
       " '20130417',\n",
       " '20130418',\n",
       " '20130419',\n",
       " '20130422',\n",
       " '20130423',\n",
       " '20130424',\n",
       " '20130425',\n",
       " '20130426',\n",
       " '20130502',\n",
       " '20130503',\n",
       " '20130506',\n",
       " '20130507',\n",
       " '20130508',\n",
       " '20130509',\n",
       " '20130510',\n",
       " '20130513',\n",
       " '20130514',\n",
       " '20130515',\n",
       " '20130516',\n",
       " '20130517',\n",
       " '20130520',\n",
       " '20130521',\n",
       " '20130522',\n",
       " '20130523',\n",
       " '20130524',\n",
       " '20130527',\n",
       " '20130528',\n",
       " '20130529',\n",
       " '20130530',\n",
       " '20130531',\n",
       " '20130603',\n",
       " '20130604',\n",
       " '20130605',\n",
       " '20130606',\n",
       " '20130607',\n",
       " '20130613',\n",
       " '20130614',\n",
       " '20130617',\n",
       " '20130618',\n",
       " '20130619',\n",
       " '20130620',\n",
       " '20130621',\n",
       " '20130624',\n",
       " '20130625',\n",
       " '20130626',\n",
       " '20130627',\n",
       " '20130628',\n",
       " '20130701',\n",
       " '20130702',\n",
       " '20130703',\n",
       " '20130704',\n",
       " '20130705',\n",
       " '20130708',\n",
       " '20130709',\n",
       " '20130710',\n",
       " '20130711',\n",
       " '20130712',\n",
       " '20130715',\n",
       " '20130716',\n",
       " '20130717',\n",
       " '20130718',\n",
       " '20130719',\n",
       " '20130722',\n",
       " '20130723',\n",
       " '20130724',\n",
       " '20130725',\n",
       " '20130726',\n",
       " '20130729',\n",
       " '20130730',\n",
       " '20130731',\n",
       " '20130801',\n",
       " '20130802',\n",
       " '20130805',\n",
       " '20130806',\n",
       " '20130807',\n",
       " '20130808',\n",
       " '20130809',\n",
       " '20130812',\n",
       " '20130813',\n",
       " '20130814',\n",
       " '20130815',\n",
       " '20130816',\n",
       " '20130819',\n",
       " '20130820',\n",
       " '20130821',\n",
       " '20130822',\n",
       " '20130823',\n",
       " '20130826',\n",
       " '20130827',\n",
       " '20130828',\n",
       " '20130829',\n",
       " '20130830',\n",
       " '20130902',\n",
       " '20130903',\n",
       " '20130904',\n",
       " '20130905',\n",
       " '20130906',\n",
       " '20130909',\n",
       " '20130910',\n",
       " '20130911',\n",
       " '20130912',\n",
       " '20130913',\n",
       " '20130916',\n",
       " '20130917',\n",
       " '20130918',\n",
       " '20130923',\n",
       " '20130924',\n",
       " '20130925',\n",
       " '20130926',\n",
       " '20130927',\n",
       " '20130930',\n",
       " '20131008',\n",
       " '20131009',\n",
       " '20131010',\n",
       " '20131011',\n",
       " '20131014',\n",
       " '20131015',\n",
       " '20131016',\n",
       " '20131017',\n",
       " '20131018',\n",
       " '20131021',\n",
       " '20131022',\n",
       " '20131023',\n",
       " '20131024',\n",
       " '20131025',\n",
       " '20131028',\n",
       " '20131029',\n",
       " '20131030',\n",
       " '20131031',\n",
       " '20131101',\n",
       " '20131104',\n",
       " '20131105',\n",
       " '20131106',\n",
       " '20131107',\n",
       " '20131108',\n",
       " '20131111',\n",
       " '20131112',\n",
       " '20131113',\n",
       " '20131114',\n",
       " '20131115',\n",
       " '20131118',\n",
       " '20131119',\n",
       " '20131120',\n",
       " '20131121',\n",
       " '20131122',\n",
       " '20131125',\n",
       " '20131126',\n",
       " '20131127',\n",
       " '20131128',\n",
       " '20131129',\n",
       " '20131202',\n",
       " '20131203',\n",
       " '20131204',\n",
       " '20131205',\n",
       " '20131206',\n",
       " '20131209',\n",
       " '20131210',\n",
       " '20131211',\n",
       " '20131212',\n",
       " '20131213',\n",
       " '20131216',\n",
       " '20131217',\n",
       " '20131218',\n",
       " '20131219',\n",
       " '20131220',\n",
       " '20131223',\n",
       " '20131224',\n",
       " '20131225',\n",
       " '20131226',\n",
       " '20131227',\n",
       " '20131230',\n",
       " '20131231',\n",
       " '20140102',\n",
       " '20140103',\n",
       " '20140106',\n",
       " '20140107',\n",
       " '20140108',\n",
       " '20140109',\n",
       " '20140110',\n",
       " '20140113',\n",
       " '20140114',\n",
       " '20140115',\n",
       " '20140116',\n",
       " '20140117',\n",
       " '20140120',\n",
       " '20140121',\n",
       " '20140122',\n",
       " '20140123',\n",
       " '20140124',\n",
       " '20140127',\n",
       " '20140128',\n",
       " '20140129',\n",
       " '20140130',\n",
       " '20140207',\n",
       " '20140210',\n",
       " '20140211',\n",
       " '20140212',\n",
       " '20140213',\n",
       " '20140214',\n",
       " '20140217',\n",
       " '20140218',\n",
       " '20140219',\n",
       " '20140220',\n",
       " '20140221',\n",
       " '20140224',\n",
       " ...]"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "startDate=20100101\n",
    "endDate=20200331\n",
    "\n",
    "days=list(TradedayDataProcess.getTradedays(startDate, endDate))\n",
    "mydays=days[::20]\n",
    "days"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(len(mydays)-1):\n",
    "    date1=mydays[i]\n",
    "    date2=mydays[i+1]\n",
    "    #print(f\"{date1}-{date2}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "database='DailyFactors_Gus'\n",
    "mlist=['BP','EP','Momentum','ROE','Size','Turnover']\n",
    "\n",
    "for i in range(len(mydays)-1):\n",
    "    date1=mydays[i]\n",
    "    date2=mydays[i+1]\n",
    "    myhours=24\n",
    "    database='DailyData_Gus'\n",
    "    measure='marketData'\n",
    "    b = dtparser.parse(str(date1))+ datetime.timedelta(hours=0)\n",
    "    e = dtparser.parse(str(date2)) + datetime.timedelta(hours=myhours)\n",
    "    query=f\"\"\" select * from \"{database}\".\"autogen\".\"{measure}\" where time >= {int(b.timestamp() * 1000 * 1000 * 1000)} and time < {int(e.timestamp() * 1000 * 1000* 1000)} \"\"\"\n",
    "    result=client.query(query)\n",
    "    if result!={}:\n",
    "        data=pd.DataFrame(result[measure])\n",
    "    else:\n",
    "        data=pd.DataFrame()\n",
    "    total=data\n",
    "    if total.shape[0]==0:\n",
    "        cotinue\n",
    "    total.index.names = ['date']\n",
    "    total.reset_index()\n",
    "    \n",
    "    database='DailyFactors_Gus'\n",
    "    for measure in mlist:\n",
    "        b = dtparser.parse(str(date1))+ datetime.timedelta(hours=0)\n",
    "        e = dtparser.parse(str(date2)) + datetime.timedelta(hours=myhours)\n",
    "        query=f\"\"\" select * from \"{database}\".\"autogen\".\"{measure}\" where time >= {int(b.timestamp() * 1000 * 1000 * 1000)} and time < {int(e.timestamp() * 1000 * 1000* 1000)} \"\"\"\n",
    "        #query=f\"\"\" select * from \"{database}\" \"\"\"\n",
    "        #query=f\"\"\"SELECT * FROM \\\"cpptest\\\".\\\"autogen\\\".\\\"601318.SH\\\"  where time >= '2019-09-24T09:00:00Z' and time < '2019-09-24T16:00:00Z' order by time \"\"\"\n",
    "        #query=f\"\"\"SELECT \\\"stkcd\\\",  \\\"cp\\\", \\\"OPNPRC\\\",\\\"PRECLOSE\\\", \\\"HighLimit\\\",\\\"LowLimit\\\",\\\"hp\\\",\\\"lp\\\",\\\"ts\\\",\\\"tt\\\", \\\"B1\\\",\\\"B2\\\",\\\"B3\\\",\\\"B4\\\",\\\"B5\\\",\\\"B6\\\",\\\"B7\\\",\\\"B8\\\",\\\"B9\\\",\\\"B10\\\",\\\"BV1\\\",\\\"BV2\\\",\\\"BV3\\\",\\\"BV4\\\",\\\"BV5\\\",\\\"BV6\\\",\\\"BV7\\\",\\\"BV8\\\",\\\"BV9\\\",\\\"BV10\\\",\\\"S1\\\", \\\"S2\\\",\\\"S3\\\",\\\"S4\\\",\\\"S5\\\",\\\"S6\\\",\\\"S7\\\",\\\"S8\\\",\\\"S9\\\",\\\"S10\\\",\\\"SV1\\\",\\\"SV2\\\",\\\"SV3\\\",\\\"SV4\\\",\\\"SV5\\\",\\\"SV6\\\",\\\"SV7\\\",\\\"SV8\\\",\\\"SV9\\\" ,\\\"SV10\\\" FROM \\\"cpptest\\\".\\\"autogen\\\".\\\"{measure}\\\"  where time >= '2019-09-24T09:00:00Z' and time < '2019-09-24T16:00:00Z' order by time \"\"\"\n",
    "\n",
    "        result=client.query(query)\n",
    "        if result!={}:\n",
    "            data=pd.DataFrame(result[measure])\n",
    "        else:\n",
    "            data=pd.DataFrame()\n",
    "        if data.shape[0]==0:\n",
    "            continue\n",
    "        data.index.names = ['date']\n",
    "        data.reset_index()    \n",
    "        #print(data.columns)\n",
    "        total=pd.merge(total,data,how='outer',on=['code','date'])   \n",
    "    print(f\"data{total.shape} of date {date1}-{date2} complete\")\n",
    "    if (total.shape[0]>0):\n",
    "        total['isST']=total['isST'].fillna(method='ffill')\n",
    "        filename=os.path.join(LocalFileAddress,'dailyFactors',str(date1)+'_'+str(date2)+'.h5')\n",
    "        with pd.HDFStore(filename,'a',complib='blosc:zstd',append=False,complevel=9) as store:\n",
    "            store.append('data',total,append=False,format=\"table\",data_columns=total.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "total.iloc[0]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data=total\n",
    "#data=data.drop(columns=['isST'])\n",
    "data.shape\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "filename=os.path.join('dailyMarketData.h5')\n",
    "with pd.HDFStore(filename,'r',complib='blosc:zstd',append=False,complevel=9) as store:\n",
    "    mydata=store['data']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mydata.iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5099, 61)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "database='MaoTickData20200511'\n",
    "measure='000008.SZ'\n",
    "#measure='test'\n",
    "date=20190102\n",
    "b = dtparser.parse(str(date))+ datetime.timedelta(hours=0)\n",
    "e = dtparser.parse(str(date)) + datetime.timedelta(hours=24)\n",
    "query=f\"\"\" select * from \"{database}\".\"autogen\".\"{measure}\" where time >= {int(b.timestamp() * 1000 * 1000 * 1000)} and time < {int(e.timestamp() * 1000 * 1000* 1000)} \"\"\"\n",
    "#query=f\"\"\" select * from \"{database}\" \"\"\"\n",
    "#query=f\"\"\"SELECT * FROM \\\"cpptest\\\".\\\"autogen\\\".\\\"601318.SH\\\"  where time >= '2019-09-24T09:00:00Z' and time < '2019-09-24T16:00:00Z' order by time \"\"\"\n",
    "#query=f\"\"\"SELECT \\\"stkcd\\\",  \\\"cp\\\", \\\"OPNPRC\\\",\\\"PRECLOSE\\\", \\\"HighLimit\\\",\\\"LowLimit\\\",\\\"hp\\\",\\\"lp\\\",\\\"ts\\\",\\\"tt\\\", \\\"B1\\\",\\\"B2\\\",\\\"B3\\\",\\\"B4\\\",\\\"B5\\\",\\\"B6\\\",\\\"B7\\\",\\\"B8\\\",\\\"B9\\\",\\\"B10\\\",\\\"BV1\\\",\\\"BV2\\\",\\\"BV3\\\",\\\"BV4\\\",\\\"BV5\\\",\\\"BV6\\\",\\\"BV7\\\",\\\"BV8\\\",\\\"BV9\\\",\\\"BV10\\\",\\\"S1\\\", \\\"S2\\\",\\\"S3\\\",\\\"S4\\\",\\\"S5\\\",\\\"S6\\\",\\\"S7\\\",\\\"S8\\\",\\\"S9\\\",\\\"S10\\\",\\\"SV1\\\",\\\"SV2\\\",\\\"SV3\\\",\\\"SV4\\\",\\\"SV5\\\",\\\"SV6\\\",\\\"SV7\\\",\\\"SV8\\\",\\\"SV9\\\" ,\\\"SV10\\\" FROM \\\"cpptest\\\".\\\"autogen\\\".\\\"{measure}\\\"  where time >= '2019-09-24T09:00:00Z' and time < '2019-09-24T16:00:00Z' order by time \"\"\"\n",
    "                         \n",
    "result=client.query(query)\n",
    "if result!={}:\n",
    "    data=pd.DataFrame(result[measure])\n",
    "else:\n",
    "    data=pd.DataFrame()\n",
    "data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "B1                         3.88\n",
       "B10                        3.79\n",
       "B2                         3.87\n",
       "B3                         3.86\n",
       "B4                         3.85\n",
       "B5                         3.84\n",
       "B6                         3.83\n",
       "B7                         3.82\n",
       "B8                         3.81\n",
       "B9                          3.8\n",
       "BV1                      119200\n",
       "BV10                      66600\n",
       "BV2                       63687\n",
       "BV3                      149300\n",
       "BV4                      234600\n",
       "BV5                      346600\n",
       "BV6                      113800\n",
       "BV7                      107200\n",
       "BV8                      412400\n",
       "BV9                      525400\n",
       "S1                         3.89\n",
       "S10                        3.98\n",
       "S2                          3.9\n",
       "S3                         3.91\n",
       "S4                         3.92\n",
       "S5                         3.93\n",
       "S6                         3.94\n",
       "S7                         3.95\n",
       "S8                         3.96\n",
       "S9                         3.97\n",
       "                        ...    \n",
       "SV10                      88700\n",
       "SV2                      551100\n",
       "SV3                      184700\n",
       "SV4                      105833\n",
       "SV5                       20600\n",
       "SV6                       48800\n",
       "SV7                      184100\n",
       "SV8                      116300\n",
       "SV9                       48000\n",
       "amount                 18196566\n",
       "amountIncrease                0\n",
       "code                  000008.SZ\n",
       "dailyOpen                  3.89\n",
       "dailyPreClose              3.89\n",
       "date                   20190102\n",
       "highLimit                  4.28\n",
       "lastPrice                  3.88\n",
       "localRecordTime       105603143\n",
       "lowLimit                    3.5\n",
       "midPrice                  3.885\n",
       "realData                      1\n",
       "tick                  105500000\n",
       "total_ask_size          4713389\n",
       "total_bid_size          2671787\n",
       "tradeStatus                 2  \n",
       "transactions_count         2165\n",
       "volume                  4695513\n",
       "volumeIncrease                0\n",
       "weightedAvgAsk             4.08\n",
       "weightedAvgBid             3.79\n",
       "Name: 2019-01-02 10:55:00+00:00, Length: 61, dtype: object"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.iloc[2000]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['buySlip']=data['S1'].rolling(3).min().shift(-3)-data['S1']\n",
    "data['sellSlip']=-data['B1'].rolling(3).max().shift(-3)+data['B1']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data[['S1','buySlip','B1','sellSlip']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4491, 146)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "database='MaoTickFactors20191118'\n",
    "database='MaoTickFactors20200430'\n",
    "measure='000008.SZ'\n",
    "measure='601899.SH'\n",
    "date=20190102\n",
    "date2=date\n",
    "b = dtparser.parse(str(date))+ datetime.timedelta(hours=0)\n",
    "e = dtparser.parse(str(date2)) + datetime.timedelta(hours=24)\n",
    "query=f\"\"\" select * from \"{database}\".\"autogen\".\"{measure}\" where time >= {int(b.timestamp() * 1000 * 1000 * 1000)} and time < {int(e.timestamp() * 1000 * 1000* 1000)} \"\"\"\n",
    "#query=f\"\"\" select * from \"{database}\" \"\"\"\n",
    "#query=f\"\"\"SELECT * FROM \\\"cpptest\\\".\\\"autogen\\\".\\\"601318.SH\\\"  where time >= '2019-09-24T09:00:00Z' and time < '2019-09-24T16:00:00Z' order by time \"\"\"\n",
    "#query=f\"\"\"SELECT \\\"stkcd\\\",  \\\"cp\\\", \\\"OPNPRC\\\",\\\"PRECLOSE\\\", \\\"HighLimit\\\",\\\"LowLimit\\\",\\\"hp\\\",\\\"lp\\\",\\\"ts\\\",\\\"tt\\\", \\\"B1\\\",\\\"B2\\\",\\\"B3\\\",\\\"B4\\\",\\\"B5\\\",\\\"B6\\\",\\\"B7\\\",\\\"B8\\\",\\\"B9\\\",\\\"B10\\\",\\\"BV1\\\",\\\"BV2\\\",\\\"BV3\\\",\\\"BV4\\\",\\\"BV5\\\",\\\"BV6\\\",\\\"BV7\\\",\\\"BV8\\\",\\\"BV9\\\",\\\"BV10\\\",\\\"S1\\\", \\\"S2\\\",\\\"S3\\\",\\\"S4\\\",\\\"S5\\\",\\\"S6\\\",\\\"S7\\\",\\\"S8\\\",\\\"S9\\\",\\\"S10\\\",\\\"SV1\\\",\\\"SV2\\\",\\\"SV3\\\",\\\"SV4\\\",\\\"SV5\\\",\\\"SV6\\\",\\\"SV7\\\",\\\"SV8\\\",\\\"SV9\\\" ,\\\"SV10\\\" FROM \\\"cpptest\\\".\\\"autogen\\\".\\\"{measure}\\\"  where time >= '2019-09-24T09:00:00Z' and time < '2019-09-24T16:00:00Z' order by time \"\"\"\n",
    "                         \n",
    "result=client.query(query)\n",
    "if result!={}:\n",
    "    data=pd.DataFrame(result[measure])\n",
    "else:\n",
    "    data=pd.DataFrame()\n",
    "data.shape\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>B1</th>\n",
       "      <th>BV1</th>\n",
       "      <th>EMABuyForce15</th>\n",
       "      <th>EMABuySellAmountWeightedPressure1_10_80</th>\n",
       "      <th>EMABuySellPress1_10</th>\n",
       "      <th>EMABuySellVolumeWeightedPressure1_10_80</th>\n",
       "      <th>EMASellForce15</th>\n",
       "      <th>S1</th>\n",
       "      <th>SV1</th>\n",
       "      <th>activeBuying</th>\n",
       "      <th>...</th>\n",
       "      <th>vwap15sTo5m</th>\n",
       "      <th>vwap15sToBV10m</th>\n",
       "      <th>vwap15sToBV30m</th>\n",
       "      <th>vwap15sToVwap</th>\n",
       "      <th>vwap1Tick</th>\n",
       "      <th>vwap1m</th>\n",
       "      <th>vwap2m</th>\n",
       "      <th>vwap30m</th>\n",
       "      <th>vwap5m</th>\n",
       "      <th>vwapToday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>0 rows × 143 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [B1, BV1, EMABuyForce15, EMABuySellAmountWeightedPressure1_10_80, EMABuySellPress1_10, EMABuySellVolumeWeightedPressure1_10_80, EMASellForce15, S1, SV1, activeBuying, activeBuyingAmount, activeBuyingAmountTotal10m, activeBuyingAmountTotal2m, activeBuyingAmountTotal30m, activeBuyingAmountTotal5m, activeBuyingIncrease, activeBuyingIncreaseMean10m, activeBuyingIncreaseMean2m, activeBuyingIncreaseMean30m, activeBuyingIncreaseMean5m, activeBuyingSellingAmountRatio10m, activeBuyingSellingAmountRatio2m, activeBuyingSellingAmountRatio30m, activeBuyingSellingAmountRatio5m, activeSelling, activeSellingAmount, activeSellingAmountTotal10m, activeSellingAmountTotal2m, activeSellingAmountTotal30m, activeSellingAmountTotal5m, activeSellingDecrease, activeSellingDecreaseMean10m, activeSellingDecreaseMean2m, activeSellingDecreaseMean30m, activeSellingDecreaseMean5m, amount1Tick, buyAverageAmountWeighted1_10_80, buyAverageVolume1_10, buyAverageVolumeWeighted1_10_80, buyForce, buySellAmountWeightedPressure1_10_80, buySellForceChange, buySellPress1_10, buySellSpread, buySellVolumeRatio10, buySellVolumeRatio2, buySellVolumeRatio5, buySellVolumeWeightedPressure1_10_80, buySellVwap10Spread, buySellVwap2Spread, buySellVwap5Spread, buySellWeightedVolumeRatio10, buySellWeightedVolumeRatio2, buySellWeightedVolumeRatio5, buyVolume10, buyVolume2, buyVolume5, buyVwap1, buyVwap10, buyVwap2, buyVwap5, buyWeightedVolume10, buyWeightedVolume2, buyWeightedVolume5, code, date, differenceHighLow10m, differenceHighLow30m, increaseToday, logbuySellSpread, logbuySellVwap10Spread, logbuySellVwap2Spread, logbuySellVwap5Spread, maxVwap15sPrice10m, maxVwap15sPrice30m, midInBuySellVwap1, midInBuySellVwap10, midInBuySellVwap2, midInBuySellVwap5, midIncreaseNext10m, midPrice, minVwap15sPrice10m, minVwap15sPrice30m, openToPreClose, realData, sellAverageAmountWeighted1_10_80, sellAverageVolume1_10, sellAverageVolumeWeighted1_10_80, sellForce, sellVolume10, sellVolume2, sellVolume5, sellVwap1, sellVwap10, sellVwap2, sellVwap5, sellWeightedVolume10, sellWeightedVolume2, sellWeightedVolume5, tick, ...]\n",
       "Index: []\n",
       "\n",
       "[0 rows x 143 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "errorData=data[data.isna().sum(axis=1)>0]\n",
    "errorData\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "B1                                                3.9\n",
       "BV1                                             36300\n",
       "EMABuyForce15                                 8.04171\n",
       "EMABuySellAmountWeightedPressure1_10_80     -0.136383\n",
       "EMABuySellPress1_10                         -0.251799\n",
       "EMABuySellVolumeWeightedPressure1_10_80     -0.258267\n",
       "EMASellForce15                                8.36222\n",
       "S1                                               3.91\n",
       "SV1                                            125300\n",
       "activeBuying                                        1\n",
       "activeBuyingAmount                              17595\n",
       "activeBuyingAmountTotal10m                     182388\n",
       "activeBuyingAmountTotal2m                      182388\n",
       "activeBuyingAmountTotal30m                     182388\n",
       "activeBuyingAmountTotal5m                      182388\n",
       "activeBuyingIncrease                       0.00128041\n",
       "activeBuyingIncreaseMean10m                0.00064627\n",
       "activeBuyingIncreaseMean2m                 0.00064627\n",
       "activeBuyingIncreaseMean30m                0.00064627\n",
       "activeBuyingIncreaseMean5m                 0.00064627\n",
       "activeBuyingSellingAmountRatio10m            0.642625\n",
       "activeBuyingSellingAmountRatio2m             0.642625\n",
       "activeBuyingSellingAmountRatio30m            0.642625\n",
       "activeBuyingSellingAmountRatio5m             0.642625\n",
       "activeSelling                                       0\n",
       "activeSellingAmount                                 0\n",
       "activeSellingAmountTotal10m                    101429\n",
       "activeSellingAmountTotal2m                     101429\n",
       "activeSellingAmountTotal30m                    101429\n",
       "activeSellingAmountTotal5m                     101429\n",
       "activeSellingDecrease                               0\n",
       "activeSellingDecreaseMean10m              -0.00116928\n",
       "activeSellingDecreaseMean2m               -0.00116928\n",
       "activeSellingDecreaseMean30m              -0.00116928\n",
       "activeSellingDecreaseMean5m               -0.00116928\n",
       "amount1Tick                                     17595\n",
       "buyAverageAmountWeighted1_10_80               11.2567\n",
       "buyAverageVolume1_10                          10.6368\n",
       "buyAverageVolumeWeighted1_10_80               9.90265\n",
       "buyForce                                      9.53021\n",
       "buySellAmountWeightedPressure1_10_80         -0.14427\n",
       "buySellForceChange                           -0.32051\n",
       "buySellPress1_10                            -0.317755\n",
       "buySellSpread                              0.00256082\n",
       "buySellVolumeRatio10                         0.421223\n",
       "buySellVolumeRatio2                          0.414403\n",
       "buySellVolumeRatio5                          0.467707\n",
       "buySellVolumeWeightedPressure1_10_80        -0.274977\n",
       "buySellVwap10Spread                         0.0245768\n",
       "buySellVwap2Spread                         0.00435234\n",
       "buySellVwap5Spread                          0.0122337\n",
       "buySellWeightedVolumeRatio10                 0.348323\n",
       "buySellWeightedVolumeRatio2                  0.303918\n",
       "buySellWeightedVolumeRatio5                  0.340801\n",
       "buyVolume10                                    416400\n",
       "buyVolume2                                      96100\n",
       "buyVolume5                                     239700\n",
       "buyVwap1                                          3.9\n",
       "buyVwap10                                     3.86018\n",
       "buyVwap2                                      3.89378\n",
       "buyVwap5                                      3.87869\n",
       "buyWeightedVolume10                           12.0994\n",
       "buyWeightedVolume2                            11.6304\n",
       "buyWeightedVolume5                             11.941\n",
       "code                                        000008.SZ\n",
       "date                                         20190102\n",
       "differenceHighLow10m                       0.00257449\n",
       "differenceHighLow30m                       0.00257449\n",
       "increaseToday                              0.00385604\n",
       "logbuySellSpread                             -5.96353\n",
       "logbuySellVwap10Spread                       -3.70554\n",
       "logbuySellVwap2Spread                        -5.43475\n",
       "logbuySellVwap5Spread                        -4.40274\n",
       "maxVwap15sPrice10m                            3.90505\n",
       "maxVwap15sPrice30m                            3.90505\n",
       "midInBuySellVwap1                                   0\n",
       "midInBuySellVwap10                         -0.0329426\n",
       "midInBuySellVwap2                            0.160318\n",
       "midInBuySellVwap5                           0.0507337\n",
       "midIncreaseNext10m                         -0.0112909\n",
       "midPrice                                        3.905\n",
       "minVwap15sPrice10m                              3.895\n",
       "minVwap15sPrice30m                              3.895\n",
       "openToPreClose                                      0\n",
       "realData                                            1\n",
       "sellAverageAmountWeighted1_10_80              11.5473\n",
       "sellAverageVolume1_10                         10.9546\n",
       "sellAverageVolumeWeighted1_10_80              10.1776\n",
       "sellForce                                           0\n",
       "sellVolume10                                   572150\n",
       "sellVolume2                                    135800\n",
       "sellVolume5                                    272800\n",
       "sellVwap1                                        3.91\n",
       "sellVwap10                                    3.95615\n",
       "sellVwap2                                     3.91077\n",
       "sellVwap5                                     3.92646\n",
       "sellWeightedVolume10                          12.7258\n",
       "sellWeightedVolume2                           12.4591\n",
       "sellWeightedVolume5                           12.6007\n",
       "tick                                        093030000\n",
       "totalVolume10                                  988550\n",
       "volume1tick                                      4500\n",
       "vwap10m                                       3.90395\n",
       "vwap15s                                       3.90267\n",
       "vwap15sBV10m                               0.00044918\n",
       "vwap15sBV30m                               0.00044918\n",
       "vwap15sInOpenAndClose                         12.6715\n",
       "vwap15sInPrevious10m                         0.236471\n",
       "vwap15sInPrevious10mByStd                   -0.835815\n",
       "vwap15sInPrevious30m                         0.236471\n",
       "vwap15sInPrevious30mByStd                   -0.835815\n",
       "vwap15sIncreaseToOpen                      0.00325745\n",
       "vwap15sIncreaseToPreClose                  0.00325745\n",
       "vwap15sPriceIncrease                       0.00036876\n",
       "vwap15sPriceIncreasePrevious10m            0.00196957\n",
       "vwap15sPriceIncreasePrevious1m             0.00196957\n",
       "vwap15sPriceIncreasePrevious2m             0.00196957\n",
       "vwap15sPriceIncreasePrevious30m            0.00196957\n",
       "vwap15sPriceIncreasePrevious5m             0.00196957\n",
       "vwap15sQuantile10m                                0.5\n",
       "vwap15sQuantile2m                                 0.5\n",
       "vwap15sQuantile30m                                0.5\n",
       "vwap15sQuantile5m                                 0.5\n",
       "vwap15sSharpe10m                              0.65111\n",
       "vwap15sSharpe2m                               0.65111\n",
       "vwap15sSharpe30m                              0.65111\n",
       "vwap15sSharpe5m                               0.65111\n",
       "vwap15sStd10m                              0.00019557\n",
       "vwap15sStd30m                              0.00019557\n",
       "vwap15sTo10m                              -0.00032691\n",
       "vwap15sTo1m                               -0.00032691\n",
       "vwap15sTo2m                               -0.00032691\n",
       "vwap15sTo30m                              -0.00032691\n",
       "vwap15sTo5m                               -0.00032691\n",
       "vwap15sToBV10m                                4.38481\n",
       "vwap15sToBV30m                                4.38481\n",
       "vwap15sToVwap                              0.00156544\n",
       "vwap1Tick                                        3.91\n",
       "vwap1m                                        3.90395\n",
       "vwap2m                                        3.90395\n",
       "vwap30m                                       3.90395\n",
       "vwap5m                                        3.90395\n",
       "vwapToday                                     3.89657\n",
       "Name: 2019-01-02 09:30:30+00:00, dtype: object"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.set_option('display.max_rows', 200)\n",
    "data.iloc[10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "client = influxdb.DataFrameClient(host='192.168.58.71', port='8086', username='root', password='root', database='')\n",
    "#client = influxdb.DataFrameClient(host='192.168.38.2', port='8086', username='root', password='root', database='')\n",
    "database='MaoTickPredict1m2m5m8003weeks'\n",
    "database='MaoTickPredict2020New'\n",
    "measure='603883.SH'\n",
    "#measure='test'\n",
    "date=20200401\n",
    "timeStart=time.time()\n",
    "b = dtparser.parse(str(date))+ datetime.timedelta(hours=0)\n",
    "e = dtparser.parse(str(date)) + datetime.timedelta(hours=24000)\n",
    "query=f\"\"\" select * from \"{database}\".\"autogen\".\"{measure}\" where time >= {int(b.timestamp() * 1000 * 1000 * 1000)} and time < {int(e.timestamp() * 1000 * 1000* 1000)} \"\"\"\n",
    "#query=f\"\"\" select * from \"{database}\" \"\"\"\n",
    "#query=f\"\"\"SELECT * FROM \\\"cpptest\\\".\\\"autogen\\\".\\\"601318.SH\\\"  where time >= '2019-09-24T09:00:00Z' and time < '2019-09-24T16:00:00Z' order by time \"\"\"\n",
    "#query=f\"\"\"SELECT \\\"stkcd\\\",  \\\"cp\\\", \\\"OPNPRC\\\",\\\"PRECLOSE\\\", \\\"HighLimit\\\",\\\"LowLimit\\\",\\\"hp\\\",\\\"lp\\\",\\\"ts\\\",\\\"tt\\\", \\\"B1\\\",\\\"B2\\\",\\\"B3\\\",\\\"B4\\\",\\\"B5\\\",\\\"B6\\\",\\\"B7\\\",\\\"B8\\\",\\\"B9\\\",\\\"B10\\\",\\\"BV1\\\",\\\"BV2\\\",\\\"BV3\\\",\\\"BV4\\\",\\\"BV5\\\",\\\"BV6\\\",\\\"BV7\\\",\\\"BV8\\\",\\\"BV9\\\",\\\"BV10\\\",\\\"S1\\\", \\\"S2\\\",\\\"S3\\\",\\\"S4\\\",\\\"S5\\\",\\\"S6\\\",\\\"S7\\\",\\\"S8\\\",\\\"S9\\\",\\\"S10\\\",\\\"SV1\\\",\\\"SV2\\\",\\\"SV3\\\",\\\"SV4\\\",\\\"SV5\\\",\\\"SV6\\\",\\\"SV7\\\",\\\"SV8\\\",\\\"SV9\\\" ,\\\"SV10\\\" FROM \\\"cpptest\\\".\\\"autogen\\\".\\\"{measure}\\\"  where time >= '2019-09-24T09:00:00Z' and time < '2019-09-24T16:00:00Z' order by time \"\"\"\n",
    "                         \n",
    "result=client.query(query)\n",
    "if result!={}:\n",
    "    data=pd.DataFrame(result[measure])\n",
    "else:\n",
    "    data=pd.DataFrame()\n",
    "print(data.shape)\n",
    "timeEnd=time.time()\n",
    "# print(data[['midIncreaseMinNext1m','predictMinNext1m']].corr().iloc[0,1])\n",
    "# print(data[['midIncreaseMaxNext1m','predictMaxNext1m']].corr().iloc[0,1])\n",
    "# print(r2_score(data['midIncreaseMinNext1m'],data['predictMinNext1m']))\n",
    "# print(r2_score(data['midIncreaseMaxNext1m'],data['predictMaxNext1m']))\n",
    "\n",
    "# print(data[data['predictMaxNext1m']>0.2]['midIncreaseMaxNext1m'].mean())\n",
    "# print(data[data['predictMinNext1m']<-0.2]['midIncreaseMinNext1m'].mean())\n",
    "# print(data[data['predictMaxNext1m']>0.2]['predictMaxNext1m'].mean())\n",
    "# print(data[data['predictMinNext1m']<-0.2]['predictMinNext1m'].mean())\n",
    "data['predictMax']=(data['predictMaxNext1m']+data['predictMaxNext2m']+data['predictMaxNext5m'])/3\n",
    "data['predictMin']=(data['predictMinNext1m']+data['predictMinNext2m']+data['predictMinNext5m'])/3\n",
    "data['max']=(data['midIncreaseMaxNext1m']+data['midIncreaseMaxNext2m']+data['midIncreaseMaxNext5m'])/3\n",
    "data['min']=(data['midIncreaseMinNext1m']+data['midIncreaseMinNext2m']+data['midIncreaseMinNext5m'])/3\n",
    "new=data[['predictMax','predictMin','max','min']].astype('float')\n",
    "new['min']=np.round(new['min'],4)\n",
    "new['max']=np.round(new['max'],4)\n",
    "print(abs(new[new['predictMax']>0.3]['predictMax']-new[new['predictMax']>0.3]['max']).mean())\n",
    "print(new[new['predictMax']>0.3]['max'].shape)\n",
    "print(abs(new[new['predictMin']<-0.3]['predictMin']-new[new['predictMin']<-0.3]['min']).mean())\n",
    "print(new[new['predictMin']<-0.3]['min'].shape)\n",
    "print(timeEnd-timeStart)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(abs(new[new['predictMax']>0.3]['predictMax']-new[new['predictMax']>0.3]['max']).mean())\n",
    "print(new[new['predictMax']>0.3]['max'].shape)\n",
    "print(abs(new[new['predictMin']<-0.3]['predictMin']-new[new['predictMin']<-0.3]['min']).mean())\n",
    "print(new[new['predictMin']<-0.3]['min'].shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['predictMax']=(data['predictMaxNext1m']+data['predictMaxNext2m']+data['predictMaxNext5m'])/3\n",
    "data['predictMin']=(data['predictMinNext1m']+data['predictMinNext2m']+data['predictMinNext5m'])/3\n",
    "data['max']=(data['midIncreaseMaxNext1m']+data['midIncreaseMaxNext2m']+data['midIncreaseMaxNext5m'])/3\n",
    "data['min']=(data['midIncreaseMinNext1m']+data['midIncreaseMinNext2m']+data['midIncreaseMinNext5m'])/3\n",
    "new=data[['predictMax','predictMin','max','min']].astype('float')\n",
    "new['min']=np.round(new['min'],4)\n",
    "new['max']=np.round(new['max'],4)\n",
    "print(new[new['max']>0.3]['max'].mean())\n",
    "print(new[new['max']>0.3]['max'].shape)\n",
    "print(new[new['min']<-0.3]['min'].mean())\n",
    "print(new[new['min']<-0.3]['min'].shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.iloc[203]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "max(new['max'],new['min'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new[(new['predictMax']>0.2) & (new['predictMin']>-0.05)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new=new.sort_values(['min'])\n",
    "new"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new[(new['predictMin']<-0.25)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new.iloc[320:400]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "plt.plot(list(new['predictMax']))\n",
    "plt.plot(list(new['max']))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.plot(list(new['predictMin']))\n",
    "plt.plot(list(new['min']))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['buySharpeNext5m'].hist(bins=1000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['sellSharpeNext5m'].hist(bins=1000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data[data['buySharpeNext5m']>0]['buySharpeNext5m'].median()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data[data['buySharpeNext5m']>0].shape[0]/data.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data[data['sellSharpeNext5m']<0].shape[0]/data.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data[(data['sellSharpeNext5m']<0) & (data['buySharpeNext5m']>0)]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data[['midIncreaseMinNext1m','predictMinNext1m']].corr().iloc[0,1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#client = influxdb.DataFrameClient(host='192.168.58.71', port='8086', username='root', password='root', database='')\n",
    "client = influxdb.DataFrameClient(host='192.168.38.2', port='8086', username='root', password='root', database='')\n",
    "database='MaoTickPredict20190909'\n",
    "measure='600519.SH'\n",
    "#measure='test'\n",
    "date=20190305\n",
    "b = dtparser.parse(str(date))+ datetime.timedelta(hours=0)\n",
    "e = dtparser.parse(str(date)) + datetime.timedelta(hours=24)\n",
    "query=f\"\"\" select * from \"{database}\".\"autogen\".\"{measure}\" where time >= {int(b.timestamp() * 1000 * 1000 * 1000)} and time < {int(e.timestamp() * 1000 * 1000* 1000)} \"\"\"\n",
    "#query=f\"\"\" select * from \"{database}\" \"\"\"\n",
    "#query=f\"\"\"SELECT * FROM \\\"cpptest\\\".\\\"autogen\\\".\\\"601318.SH\\\"  where time >= '2019-09-24T09:00:00Z' and time < '2019-09-24T16:00:00Z' order by time \"\"\"\n",
    "#query=f\"\"\"SELECT \\\"stkcd\\\",  \\\"cp\\\", \\\"OPNPRC\\\",\\\"PRECLOSE\\\", \\\"HighLimit\\\",\\\"LowLimit\\\",\\\"hp\\\",\\\"lp\\\",\\\"ts\\\",\\\"tt\\\", \\\"B1\\\",\\\"B2\\\",\\\"B3\\\",\\\"B4\\\",\\\"B5\\\",\\\"B6\\\",\\\"B7\\\",\\\"B8\\\",\\\"B9\\\",\\\"B10\\\",\\\"BV1\\\",\\\"BV2\\\",\\\"BV3\\\",\\\"BV4\\\",\\\"BV5\\\",\\\"BV6\\\",\\\"BV7\\\",\\\"BV8\\\",\\\"BV9\\\",\\\"BV10\\\",\\\"S1\\\", \\\"S2\\\",\\\"S3\\\",\\\"S4\\\",\\\"S5\\\",\\\"S6\\\",\\\"S7\\\",\\\"S8\\\",\\\"S9\\\",\\\"S10\\\",\\\"SV1\\\",\\\"SV2\\\",\\\"SV3\\\",\\\"SV4\\\",\\\"SV5\\\",\\\"SV6\\\",\\\"SV7\\\",\\\"SV8\\\",\\\"SV9\\\" ,\\\"SV10\\\" FROM \\\"cpptest\\\".\\\"autogen\\\".\\\"{measure}\\\"  where time >= '2019-09-24T09:00:00Z' and time < '2019-09-24T16:00:00Z' order by time \"\"\"\n",
    "                         \n",
    "result=client.query(query)\n",
    "if result!={}:\n",
    "    data=pd.DataFrame(result[measure])\n",
    "else:\n",
    "    data=pd.DataFrame()\n",
    "print(data[['midIncreaseMinNext1m','predictMinNext1m']].corr().iloc[0,1])\n",
    "print(data[['midIncreaseMaxNext1m','predictMaxNext1m']].corr().iloc[0,1])\n",
    "print(r2_score(data['midIncreaseMinNext1m'],data['predictMinNext1m']))\n",
    "print(r2_score(data['midIncreaseMaxNext1m'],data['predictMaxNext1m']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "client = influxdb.DataFrameClient(host='192.168.58.71', port='8086', username='root', password='root', database='')\n",
    "#client = influxdb.DataFrameClient(host='192.168.38.2', port='8086', username='root', password='root', database='')\n",
    "#database='MaoTickPredict20190909original'\n",
    "database='MaoTickPredict20190909'\n",
    "measure='000001.SZ'\n",
    "#measure='test'\n",
    "date=20190830\n",
    "date2=20190830\n",
    "b = dtparser.parse(str(date))+ datetime.timedelta(hours=0)\n",
    "e = dtparser.parse(str(date2)) + datetime.timedelta(hours=24)\n",
    "query=f\"\"\" select * from \"{database}\".\"autogen\".\"{measure}\" where time >= {int(b.timestamp() * 1000 * 1000 * 1000)} and time < {int(e.timestamp() * 1000 * 1000* 1000)} \"\"\"\n",
    "#query=f\"\"\" select * from \"{database}\" \"\"\"\n",
    "#query=f\"\"\"SELECT * FROM \\\"cpptest\\\".\\\"autogen\\\".\\\"601318.SH\\\"  where time >= '2019-09-24T09:00:00Z' and time < '2019-09-24T16:00:00Z' order by time \"\"\"\n",
    "#query=f\"\"\"SELECT \\\"stkcd\\\",  \\\"cp\\\", \\\"OPNPRC\\\",\\\"PRECLOSE\\\", \\\"HighLimit\\\",\\\"LowLimit\\\",\\\"hp\\\",\\\"lp\\\",\\\"ts\\\",\\\"tt\\\", \\\"B1\\\",\\\"B2\\\",\\\"B3\\\",\\\"B4\\\",\\\"B5\\\",\\\"B6\\\",\\\"B7\\\",\\\"B8\\\",\\\"B9\\\",\\\"B10\\\",\\\"BV1\\\",\\\"BV2\\\",\\\"BV3\\\",\\\"BV4\\\",\\\"BV5\\\",\\\"BV6\\\",\\\"BV7\\\",\\\"BV8\\\",\\\"BV9\\\",\\\"BV10\\\",\\\"S1\\\", \\\"S2\\\",\\\"S3\\\",\\\"S4\\\",\\\"S5\\\",\\\"S6\\\",\\\"S7\\\",\\\"S8\\\",\\\"S9\\\",\\\"S10\\\",\\\"SV1\\\",\\\"SV2\\\",\\\"SV3\\\",\\\"SV4\\\",\\\"SV5\\\",\\\"SV6\\\",\\\"SV7\\\",\\\"SV8\\\",\\\"SV9\\\" ,\\\"SV10\\\" FROM \\\"cpptest\\\".\\\"autogen\\\".\\\"{measure}\\\"  where time >= '2019-09-24T09:00:00Z' and time < '2019-09-24T16:00:00Z' order by time \"\"\"\n",
    "                         \n",
    "result=client.query(query)\n",
    "if result!={}:\n",
    "    data=pd.DataFrame(result[measure])\n",
    "else:\n",
    "    data=pd.DataFrame()\n",
    "print(data[['midIncreaseMinNext1m','predictMinNext1m']].corr())\n",
    "print(data[['midIncreaseMaxNext1m','predictMaxNext1m']].corr())\n",
    "print(r2_score(data['midIncreaseMinNext1m'],data['predictMinNext1m']))\n",
    "print(r2_score(data['midIncreaseMaxNext1m'],data['predictMaxNext1m']))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(r2_score(data[['midIncreaseMaxNext1m','midIncreaseMinNext1m']].values.reshape(-1),data[['predictMaxNext1m','predictMaxNext1m']].values.reshape(-1)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data[['midIncreaseMaxNext1m','midIncreaseMinNext1m']].values.reshape(-1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('display.max_rows',5000)\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#client = influxdb.DataFrameClient(host='192.168.58.71', port='8086', username='root', password='root', database='')\n",
    "client = influxdb.DataFrameClient(host='192.168.38.2', port='8086', username='root', password='root', database='')\n",
    "database='MaoTickData'\n",
    "measure='600519.SH'\n",
    "#measure='test'\n",
    "date=20191105\n",
    "b = dtparser.parse(str(date))+ datetime.timedelta(hours=0)\n",
    "e = dtparser.parse(str(date)) + datetime.timedelta(hours=24)\n",
    "query=f\"\"\" select * from \"{database}\".\"autogen\".\"{measure}\" where time >= {int(b.timestamp() * 1000 * 1000 * 1000)} and time < {int(e.timestamp() * 1000 * 1000* 1000)} \"\"\"\n",
    "#query=f\"\"\" select * from \"{database}\" \"\"\"\n",
    "#query=f\"\"\"SELECT * FROM \\\"cpptest\\\".\\\"autogen\\\".\\\"601318.SH\\\"  where time >= '2019-09-24T09:00:00Z' and time < '2019-09-24T16:00:00Z' order by time \"\"\"\n",
    "#query=f\"\"\"SELECT \\\"stkcd\\\",  \\\"cp\\\", \\\"OPNPRC\\\",\\\"PRECLOSE\\\", \\\"HighLimit\\\",\\\"LowLimit\\\",\\\"hp\\\",\\\"lp\\\",\\\"ts\\\",\\\"tt\\\", \\\"B1\\\",\\\"B2\\\",\\\"B3\\\",\\\"B4\\\",\\\"B5\\\",\\\"B6\\\",\\\"B7\\\",\\\"B8\\\",\\\"B9\\\",\\\"B10\\\",\\\"BV1\\\",\\\"BV2\\\",\\\"BV3\\\",\\\"BV4\\\",\\\"BV5\\\",\\\"BV6\\\",\\\"BV7\\\",\\\"BV8\\\",\\\"BV9\\\",\\\"BV10\\\",\\\"S1\\\", \\\"S2\\\",\\\"S3\\\",\\\"S4\\\",\\\"S5\\\",\\\"S6\\\",\\\"S7\\\",\\\"S8\\\",\\\"S9\\\",\\\"S10\\\",\\\"SV1\\\",\\\"SV2\\\",\\\"SV3\\\",\\\"SV4\\\",\\\"SV5\\\",\\\"SV6\\\",\\\"SV7\\\",\\\"SV8\\\",\\\"SV9\\\" ,\\\"SV10\\\" FROM \\\"cpptest\\\".\\\"autogen\\\".\\\"{measure}\\\"  where time >= '2019-09-24T09:00:00Z' and time < '2019-09-24T16:00:00Z' order by time \"\"\"\n",
    "                         \n",
    "result=client.query(query)\n",
    "if result!={}:\n",
    "    data=pd.DataFrame(result[measure])\n",
    "else:\n",
    "    data=pd.DataFrame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "database='order'\n",
    "#client.drop_database(database)\n",
    "#client.create_database(database)\n",
    "\n",
    "measure='order'\n",
    "#measure='test'\n",
    "date=20200427\n",
    "b = dtparser.parse(str(date))+ datetime.timedelta(hours=6)\n",
    "e = dtparser.parse(str(date)) + datetime.timedelta(hours=24)\n",
    "query=f\"\"\" select * from \"{database}\".\"autogen\".\"{measure}\" where time >= {int(b.timestamp() * 1000 * 1000 * 1000)} and time < {int(e.timestamp() * 1000 * 1000* 1000)} \"\"\"\n",
    "#query=f\"\"\" select * from \"{database}\" \"\"\"\n",
    "#query=f\"\"\"SELECT * FROM \\\"cpptest\\\".\\\"autogen\\\".\\\"601318.SH\\\"  where time >= '2019-09-24T09:00:00Z' and time < '2019-09-24T16:00:00Z' order by time \"\"\"\n",
    "#query=f\"\"\"SELECT \\\"stkcd\\\",  \\\"cp\\\", \\\"OPNPRC\\\",\\\"PRECLOSE\\\", \\\"HighLimit\\\",\\\"LowLimit\\\",\\\"hp\\\",\\\"lp\\\",\\\"ts\\\",\\\"tt\\\", \\\"B1\\\",\\\"B2\\\",\\\"B3\\\",\\\"B4\\\",\\\"B5\\\",\\\"B6\\\",\\\"B7\\\",\\\"B8\\\",\\\"B9\\\",\\\"B10\\\",\\\"BV1\\\",\\\"BV2\\\",\\\"BV3\\\",\\\"BV4\\\",\\\"BV5\\\",\\\"BV6\\\",\\\"BV7\\\",\\\"BV8\\\",\\\"BV9\\\",\\\"BV10\\\",\\\"S1\\\", \\\"S2\\\",\\\"S3\\\",\\\"S4\\\",\\\"S5\\\",\\\"S6\\\",\\\"S7\\\",\\\"S8\\\",\\\"S9\\\",\\\"S10\\\",\\\"SV1\\\",\\\"SV2\\\",\\\"SV3\\\",\\\"SV4\\\",\\\"SV5\\\",\\\"SV6\\\",\\\"SV7\\\",\\\"SV8\\\",\\\"SV9\\\" ,\\\"SV10\\\" FROM \\\"cpptest\\\".\\\"autogen\\\".\\\"{measure}\\\"  where time >= '2019-09-24T09:00:00Z' and time < '2019-09-24T16:00:00Z' order by time \"\"\"\n",
    "                         \n",
    "result=client.query(query)\n",
    "if result!={}:\n",
    "    data=pd.DataFrame(result[measure])\n",
    "else:\n",
    "    data=pd.DataFrame()\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "data.iloc[-40]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('display.max_rows',1000)\n",
    "data.shape\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "database='order'\n",
    "#client.drop_database(database)\n",
    "#client.create_database(database)\n",
    "#client = influxdb.DataFrameClient(host='192.168.38.152', port='8086', username='root', password='root', database='')\n",
    "measure='order'\n",
    "date=20200106\n",
    "b = dtparser.parse(str(date))+ datetime.timedelta(hours=0)\n",
    "e = dtparser.parse(str(date)) + datetime.timedelta(hours=24)\n",
    "query=f\"\"\" select * from \"{database}\".\"autogen\".\"{measure}\" where time >= {int(b.timestamp() * 1000 * 1000 * 1000)} and time < {int(e.timestamp() * 1000 * 1000* 1000)} \"\"\"\n",
    "#query=f\"\"\" select * from \"{database}\" \"\"\"\n",
    "#query=f\"\"\"SELECT * FROM \\\"cpptest\\\".\\\"autogen\\\".\\\"601318.SH\\\"  where time >= '2019-09-24T09:00:00Z' and time < '2019-09-24T16:00:00Z' order by time \"\"\"\n",
    "#query=f\"\"\"SELECT \\\"stkcd\\\",  \\\"cp\\\", \\\"OPNPRC\\\",\\\"PRECLOSE\\\", \\\"HighLimit\\\",\\\"LowLimit\\\",\\\"hp\\\",\\\"lp\\\",\\\"ts\\\",\\\"tt\\\", \\\"B1\\\",\\\"B2\\\",\\\"B3\\\",\\\"B4\\\",\\\"B5\\\",\\\"B6\\\",\\\"B7\\\",\\\"B8\\\",\\\"B9\\\",\\\"B10\\\",\\\"BV1\\\",\\\"BV2\\\",\\\"BV3\\\",\\\"BV4\\\",\\\"BV5\\\",\\\"BV6\\\",\\\"BV7\\\",\\\"BV8\\\",\\\"BV9\\\",\\\"BV10\\\",\\\"S1\\\", \\\"S2\\\",\\\"S3\\\",\\\"S4\\\",\\\"S5\\\",\\\"S6\\\",\\\"S7\\\",\\\"S8\\\",\\\"S9\\\",\\\"S10\\\",\\\"SV1\\\",\\\"SV2\\\",\\\"SV3\\\",\\\"SV4\\\",\\\"SV5\\\",\\\"SV6\\\",\\\"SV7\\\",\\\"SV8\\\",\\\"SV9\\\" ,\\\"SV10\\\" FROM \\\"cpptest\\\".\\\"autogen\\\".\\\"{measure}\\\"  where time >= '2019-09-24T09:00:00Z' and time < '2019-09-24T16:00:00Z' order by time \"\"\"\n",
    "                         \n",
    "result=client.query(query)\n",
    "if result!={}:\n",
    "    data=pd.DataFrame(result[measure])\n",
    "else:\n",
    "    data=pd.DataFrame()\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dd=data[data['filledVol']<data['placeVol']]\n",
    "s=list(dd['code'].unique())\n",
    "d=data[data['code'].isin(s)]\n",
    "d.sort_values(['code','ttime'])"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
